E-QR: PS Query Errors with "This field is an aggregate field but is being used in non-having criteria. (139,136) The query may fail if this is not corrected" When A Field Is Used As Criteria And Needs To Be Returned As Aggregate Function MIN/MAX/COUNT (Doc ID 3076764.1)

Last updated on MARCH 13, 2025

Applies to:

Symptoms

When attempting to build queries with a field returned as an aggregate (MIN/MAX/COUNT etc.) where that field is also used in criteria to join records, the following error occurs:

The issue can be reproduced at will with the following steps:
1. Navigate to Reporting Tools > Query > Query Manager
2. Create a simple query, with ACAD_PROG, STDNT_CAR_TERM and TERM_TBL using standard joins and default joining criteria.
3. Select the following 8 fields, 1-6 from ACAD_PROG, 7 from STDNT_CAR_TERM, and 8 from TERM_TBL
4. The current generated SQL is

SELECT A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ACAD_PROG, A.PROG_STATUS, A.PROG_ACTION, B.STRM, C.DESCR
  FROM PS_ACAD_PROG A, PS_STDNT_CAR_TERM B, PS_TERM_TBL C
  WHERE ( A.EFFDT =
  (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
  WHERE A.EMPLID = A_ED.EMPLID
  AND A.ACAD_CAREER = A_ED.ACAD_CAREER
  AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
  AND A_ED.EFFDT <= SYSDATE)
  AND A.EFFSEQ =
  (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
  WHERE A.EMPLID = A_ES.EMPLID
  AND A.ACAD_CAREER = A_ES.ACAD_CAREER
  AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
  AND A.EFFDT = A_ES.EFFDT)
  AND A.EMPLID = B.EMPLID
  AND A.ACAD_CAREER = B.ACAD_CAREER
  AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
  AND B.INSTITUTION = A.INSTITUTION
  AND B.ACAD_CAREER = C.ACAD_CAREER
  AND B.INSTITUTION = C.INSTITUTION
  AND B.STRM = C.STRM)

5. Click on EDIT for B.STRM and select MAX, you will get the error message
 
 The generated SQL now looks like this
SELECT A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ACAD_PROG, A.PROG_STATUS, A.PROG_ACTION, MAX( B.STRM), C.DESCR
  FROM PS_ACAD_PROG A, PS_STDNT_CAR_TERM B, PS_TERM_TBL C
  WHERE ( A.EFFDT =
  (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
  WHERE A.EMPLID = A_ED.EMPLID
  AND A.ACAD_CAREER = A_ED.ACAD_CAREER
  AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
  AND A_ED.EFFDT <= SYSDATE)
  AND A.EFFSEQ =
  (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
  WHERE A.EMPLID = A_ES.EMPLID
  AND A.ACAD_CAREER = A_ES.ACAD_CAREER
  AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
  AND A.EFFDT = A_ES.EFFDT)
  AND A.EMPLID = B.EMPLID
  AND A.ACAD_CAREER = B.ACAD_CAREER
  AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
  AND B.INSTITUTION = A.INSTITUTION
  AND B.ACAD_CAREER = C.ACAD_CAREER
  AND B.INSTITUTION = C.INSTITUTION
  AND MAX( B.STRM) = C.STRM)
  GROUP BY A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ACAD_PROG, A.PROG_STATUS, A.PROG_ACTION, C.DESCR

6. As expected, it has added MAX(B.STRM) in the returned fields and includes the correct fields in the Group By. However, it has changed B.STRM to MAX( B.STRM) in the criteria. This is not valid SQL. The AND MAX( B.STRM) = C.STRM should be left as AND B.STRM = C.STRM which is valid and would work as intended.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.