ORA-00920: invalid relational operator in select clause with multiple joins and using max value

I trying to use a select statement to return rows when the following condition are met:

First condition: The Employee's EMPLOYEE_HOME_ORG IS NOT EQUAL TO 396000 OR 396010 AND DEGREE_HIERARCHY is less than 40 AND FACULTY_DEGREE_CODE is not equal to 'MS' or 'MA'

Second Condition: The employee's DEGREE_HIERARCHY is less than 40 and the employee's FACULTY_SUBJECT_CODE and FACULTY_DEGREE_CODE is not in the table SUBJ_CODE_CROSSWALK.

I get the following error:

ORA-00920: invalid relational operator

Please see the code below, any help is greatly appreciated!

SELECT V.FACULTY_DEGREE_CODE,
      V.FACULTY_SUBJECT_CODE,
     I.EMPLOYEE_DEPARTMENT_HOME_ORG,
      MAX(D.DEGREE_HIERARCHY)
 FROM V_DEGRESS V
      JOIN DEGREE_CROSSWALK D
          ON V.FACULTY_DEGREE_CODE =
             D.DEGREE_CODE
      JOIN I_REPORT_DETAILS I
          ON V.PIDM = I.HR_PIDM
      LEFT JOIN SUBJ_CODE_CROSSWALK S
          ON V.FACULTY_DEGREE_CODE =
             S.FACULTY_DEGREE_CODE
      LEFT JOIN SUBJ_CODE_CROSSWALK S
          ON S.FACULTY_SUBJECT_CODE =
             V.FACULTY_SUBJECT_CODE
  WHERE     V.PERSON_SKEY = 12345

      AND            
     (  
       (I.EMPLOYEE_DEPARTMENT_HOME_ORG != 396000
        and I.EMPLOYEE_DEPARTMENT_HOME_ORG != 396010)
        and (d.DEGREE_HIERARCHY < 40 )
        and 
        (V.FACULTY_DEGREE_CODE != 'MS' and
            V.FACULTY_DEGREE_CODE != 'MA')
    )
             --and NOT ONE SUBJ CODE CROSSWALK
       AND  
       (
           (D.DEGREE_HIERARCHY < 40)      
           AND 
           (V.FACULTY_SUBJECT_CODE,
               V.FACULTY_DEGREE_CODE NOT IN
                    (SELECT S.FACULTY_SUBJECT_CODE,
                            S.FACULTY_DEGREE_CODE
                       FROM SUBJ_CODE_CROSSWALK
                            S
                      WHERE     S.FACULTY_SUBJECT_CODE =
                                V.FACULTY_SUBJECT_CODE
                            AND V.FACULTY_DEGREE_CODE =
                                S.FACULTY_DEGREE_CODE
                    )
             )
          )
GROUP BY V.FACULTY_DEGREE_CODE,
      V.FACULTY_SUBJECT_CODE,
      I.EMPLOYEE_DEPARTMENT_HOME_ORG

Solution

Aside the fact that there's probably a typo since you are joining the table twice with the same alias, there's a syntax error in your query.

The second condition includes a tuple but is incorrectly typed. It should be in parenthesis as in (V.FACULTY_SUBJECT_CODE, V.FACULTY_DEGREE_CODE). Change:

AND 
       (V.FACULTY_SUBJECT_CODE,
           V.FACULTY_DEGREE_CODE NOT IN
                (SELECT S.FACULTY_SUBJECT_CODE,
                        S.FACULTY_DEGREE_CODE
                   FROM ...

For:

AND 
       (V.FACULTY_SUBJECT_CODE,
           V.FACULTY_DEGREE_CODE) NOT IN
                (SELECT S.FACULTY_SUBJECT_CODE,
                        S.FACULTY_DEGREE_CODE
                   FROM ...

Also, you'll need to remove one parenthesis at then end to ensure they are correctly balanced.

NOTE: You need to make sure the columns S.FACULTY_SUBJECT_CODE and S.FACULTY_DEGREE_CODE do not include nulls. If they do, this syntax won't produce the results you want. You'll need to rephrase this section as an "anti-join".