E-QR: Query SQL Group By Clause Has A Different Field Name Than What Was Selected (Doc ID 2034584.1)

Last updated on AUGUST 17, 2015

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 and later
Information in this document applies to any platform.

Symptoms

After upgrade to PeopleTools 8.54.09, when attempting to run queries with translate tables and Group By clause the SQL generated has a different name in the Group BY clause than in the select statement and the following error occurs:

"A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=979, Message=ORA-00979: not
a GROUP BY expression (50,380)"

Steps
-------
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Create a new PS Query using UNION (e.g. SB_TEST_TRANSLATE_GROUPBY)
4. View Query SQL

SELECT A.EMPLID, MAX( A.DEPTID), A.HR_STATUS,  A4X.XLATLONGNAME, CASE WHEN (
A.EMPL_STATUS = 'A') THEN 'Test' END
  FROM PS_JOB A LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE
TA.FIELDNAME='EMPL_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = (SELECT
MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='EMPL_STATUS' AND
TA.FIELDVALUE=TB.FIELDVALUE AND  TB.EFF_STATUS = 'A' AND TB.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))) A4X ON A4X.FIELDVALUE =
A.EMPL_STATUS AND A4X.FIELDNAME='EMPL_STATUS'  , PS_EMPLMT_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
   AND A.EMPL_RCD = A1.EMPL_RCD
   AND A1.OPRID = 'PS'
   AND ( A.EFFDT =
       (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
       WHERE A.EMPLID = A_ED.EMPLID
         AND A.EMPL_RCD = A_ED.EMPL_RCD
         AND A_ED.EFFDT <= SYSDATE)
   AND A.EFFSEQ =
       (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
       WHERE A.EMPLID = A_ES.EMPLID
         AND A.EMPL_RCD = A_ES.EMPL_RCD
         AND A.EFFDT = A_ES.EFFDT) ))
 GROUP BY  A.EMPLID,  A.HR_STATUS,   A4X.XLATLONGNAME,  CASE WHEN (  
A4X.XLATLONGNAME = 'A') THEN 'Test' END


5. Go to RUN tab and display the output results - the following error occurs:
"A SQL error occurred. Please consult your system log for details. Error in running query because of SQL Error, Code=979, Message=ORA-00979: not
a GROUP BY expression (50,380)"

Note:
In order to join the translate table, go to Fields tab, then hit the 'Edit' button near the 'A.EMPL_STATUS - Payroll Status' field and under 'Translate Value' section select the radio check button 'Long'.


Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms