Sql query with max value in where condition

I have a query which should return one row if the conditions are met. This query compares columns from 3 tables and should use the max value of DEGREE_HIERARCHY since there can be more than one value. It then should use the max(DEGREE_HIERARCHY) in the where clause to satisfy the conditions.

The code I have so far has the error:

[Error] Execution (1: 8): ORA-00937: not a single-group group function

Any help is appreciated thanks!

SELECT V.FACULTY_DEGREE_CODE,
        V.FACULTY_SUBJECT_CODE,
        I.EMPLOYEE_DEPARTMENT_HOME_ORG,
        MAX(D.DEGREE_HIERARCHY)
        
   FROM V_degrees  V
        JOIN DEGREE_CROSSWALK D
            ON V.FACULTY_DEGREE_CODE =
               D.DEGREE_CODE
        JOIN I_DETAILS I
            ON V.ID = I.HR_ID
        INNER JOIN  SUBJ_CROSSWALK  S
             ON V.FACULTY_DEGREE_CODE =
              S.FACULTY_DEGREE_CODE
            INNER JOIN SUBJ_CROSSWALK  S
                ON S.FACULTY_SUBJECT_CODE =
                   V.FACULTY_SUBJECT_CODE
  WHERE V.PERSON_id = 12345
  AND 
  (
   (I.EMPLOYEE_DEPARTMENT_HOME_ORG != '396000'
    OR I.EMPLOYEE_DEPARTMENT_HOME_ORG != '396010'
    ) and
    (DEGREE_HIERARCHY > 30)
  )
  AND 
  (
    (DEGREE_HIERARCHY > 30) AND
    (NOT EXISTS (SELECT FACULTY_SUBJECT_CODE, FACULTY_DEGREE_CODE 
                FROM SUBJ_CROSSWALK  S
                WHERE S.FACULTY_SUBJECT_CODE = V.FACULTY_SUBJECT_CODE
                AND
                V.FACULTY_DEGREE_CODE = S.FACULTY_DEGREE_CODE)
    )
  )

Answer

Unaggregated columns in the SELECT list must be present in the GROUP BY clause… that you forgot to add. Just add at the end:

GROUP BY V.FACULTY_DEGREE_CODE,
         V.FACULTY_SUBJECT_CODE,
         I.EMPLOYEE_DEPARTMENT_HOME_ORG

Advertisement

Answer