PeopleTools and Lifecycle Management - PSFT (MOSC)

MOSC Banner

No auto group by clause for having criteria in Peoplesoft Query

edited May 5, 2020 10:12AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 17 commentsAnswered

Hello,

I am creating a query which has a having criteria, the having criteria does not automatically add a group by clause like we get when aggregate function is used in the select clause.Thereby my query resuts in " not a GROUP BY expression (50,380)"  error.
Kindly let me know how to proceed.

Query :

SELECT A.PRCSINSTANCE, A.PRCSNAME, A.MAINJOBNAME, A.SERVERNAMERUN, A.OPRID, A.RUNCNTLID

  FROM PSPRCSRQST A, PSPRCSRQST B

  WHERE ( A.RUNSTATUS = '7'

     AND A.PRCSINSTANCE = B.PRCSINSTANCE

     AND B.PRCSNAME = A.PRCSNAME

     AND B.RUNSTATUS = '9')

  HAVING ( avg(    (( (  to_char( B.ENDDTTM ,'DD'))- (to_char( B.BEGINDTTM ,'DD')))* 24* 60*60) +   (    ( (  to_char( B.ENDDTTM ,'HH'))- (to_char( B.BEGINDTTM ,'HH'))) * 60*60) + (((  to_char( B.ENDDTTM ,'MI'))- (

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center