Oracle 12c – ORA-00933 Error with Match_recognize

oracleoracle-12cpattern matching

Am trying to figure out was wrong with this simple query that uses MATCH_RECOGNIZE. Am getting the error ORA-00933: SQL command not properly ended. What could I be missing?

    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))
)

Best Answer

When I first ran your query on 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

I didn't get ORA-0933 as you have mentioned. But when I ran your code on Oracle 11.2.0.4 then I got the same error as you have said. And the reason is, the MATCH_RECOGNIZE clause is added on analytic function syntax from Oracle 12c only.

Then on 12c, I corrected the error as-

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))
);

which runs perfectly for me, at least without any syntax error.

Related Question