Hi All,
We have this query in our application which works fine in 9i but fails in 11gR2 (on Exadata) giving an "ORA-00937: not a single-group group function" error.
SELECT A.CNTRL_ID,
B.FILEID,
C.VNDR_ID,
D.STRT_DT,
D.END_DT,
COUNT ( * ) TOT_REC,
COUNT (DECODE (PROCESSED_FILE, 'Y', 1, NULL)) PROCESSED,
COUNT (DECODE (PROCESSED_FILE, 'N', 1, NULL)) NOT_PROCESSED
FROM EMPLOYEE B,
EXTRACT_CONTROL D,
FILE_CONFIG C
WHERE C.FILEID = B.FILEID
AND C.CONFIG_ID = D.CONFIG_ID
AND STATUS NOT IN ('INCOMP')
and (NVL(D.END_DT,TRUNC(SYSDATE)),D.CONFIG_ID)
in
(SELECT MAX(NVL(END_DT,TRUNC(SYSDATE))),C.CONFIG_ID
from EXTRACT_CONTROL C WHERE C.CONFIG_ID = D.CONFIG_ID
AND C.STATUS not in ('INCOMP','FAILED')
)
GROUP BY CNTRL_ID,
B.FILEID,
C.VNDR_ID,
D.STRT_DT,
D.END_DT
The subquery is selecting a column and it doesn't have a group by clause at all. I am not sure how is this even working in 9i. I always thought that on a simple query using an aggregate function (without any analytic functions / clause), we cannot select a column without having that column in the group by clause. So, how 11g behaves was not a surprise but surprised to see how 9i behaves. Can someone explain this behaviour?
Thanks,
Prathap