Oracle-12c
公用表表達式
Oracle 12 c 中是否有替代公用表表達式的方法?我一直在使用 SQL Server 的語法,而 oracle 給了我以下錯誤。
SELECT CTE_TEST AS (SELECT DISTINCT deptno, ename FROM Test_Table ) SELECT * FROM CTE;
命令行錯誤:43 列:20 錯誤報告 - SQL 錯誤:ORA-00923:FROM 關鍵字未在預期的位置找到 00923。00000 -“FROM 關鍵字未在預期的位置找到” *原因:
*操作:
SQL> with cte_test 2 as 3 (select * from emp); (select * from emp) * ERROR at line 3: ORA-00928: missing SELECT keyword SQL> select cte_test as (select * from emp) 2 select * from cte; select cte_test as (select * from emp) * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> with cte_test as (select * from emp) 2 select * from cte_test; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 12 rows selected. SQL>