带有Match_recognize的ORA-00933

Question

我试图找出这个使用MATCH_RECOGNIZE的简单查询是错误的。我正在获得错误ORA-00933: SQL命令没有正确结束。我错过了什么？

    SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES 
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)

Answer 1

当我第一次在SQL*PLUS (Oracle 12.2)上运行您的查询时

SQL>  SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES 
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)  2    3    4    5    6    7    8    9   10   11  ;
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
            *
ERROR at line 6:
ORA-62505: expression needs to be aliased

我没有像你提到的那样得到ORA-0933。但是，当我在Oracle 11.2.0.4上运行您的代码时，我得到了与您说的相同的错误。原因是，MATCH_RECOGNIZE子句仅在Oracle12c中的解析函数语法上添加。

然后在12c，我把错误更正为-

SELECT emp_id,dept_id,SAL FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY salary
MEASURES 
 EMPLOYEE_ID as emp_id,
 DEPARTMENT_ID as dept_id,
 SALARY as sal
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.salary < PREV(B.salary))
);

它对我来说很完美，至少没有任何语法错误。