主页 / dba / 问题 / 171477
Accepted
Nicholas Namacha
Asked: 2017-04-20 20:04:49 +0800 CST

ORA-00933 与 Match_recognize

  • 772

我试图弄清楚这个使用 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))
)
oracle oracle-12c

1 个回答

  1. Best Answer

    当我第一次在 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子句仅在 Oracle 12c 的解析函数语法上添加。

    然后在 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))
);

    这对我来说运行完美，至少没有任何语法错误。

    • 1

相关问题