时间戳

首页 > 解决方案 > ORA-00920: 具有多个连接并使用最大值的选择子句中的无效关系运算符

sql - ORA-00920: 具有多个连接并使用最大值的选择子句中的无效关系运算符

问题描述

当满足以下条件时，我尝试使用 select 语句返回行：

第一个条件：员工的 EMPLOYEE_HOME_ORG 不等于 396000 或 396010 并且 DEGREE_HIERARCHY 小于 40 并且 FACULTY_DEGREE_CODE 不等于 'MS' 或 'MA'

条件二：员工的DEGREE_HIERARCHY小于40，员工的FACULTY_SUBJECT_CODE和FACULTY_DEGREE_CODE不在SUBJ_CODE_CROSSWALK表中。

我收到以下错误：

ORA-00920: invalid relational operator

请参阅下面的代码，非常感谢任何帮助！

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

标签： sqloraclemaxrelational-operators

解决方案

除了因为您使用相同的别名两次加入表而可能存在拼写错误之外，您的查询中存在语法错误。

第二个条件包含一个元组，但输入错误。它应该像在括号中一样(V.FACULTY_SUBJECT_CODE, V.FACULTY_DEGREE_CODE)。改变：

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

为了：

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

此外，您需要在末尾删除一个括号以确保它们正确平衡。

注意：您需要确保列S.FACULTY_SUBJECT_CODE并且S.FACULTY_DEGREE_CODE不包含空值。如果他们这样做，此语法将不会产生您想要的结果。您需要将此部分重新表述为“反加入”。

推荐阅读