A CTE runs well in SQL Server but does not work in Oracle 11g

Question

I am learning CTE in Oracle, and I have written a SQL statement with a CTE. I didn't find any error in this SQL in SQL Server database, but it always causes an error in Oracle 11g database;

CREATE TABLE EMP 
   (    EMPNO numeric(4,0), 
    ENAME VARCHAR(10 ), 
    JOB VARCHAR(9 ), 
    MGR numeric(4,0), 
    HIREDATE DATE, 
    SAL numeric(7,2), 
    COMM numeric(7,2), 
    DEPTNO numeric(2,0)
   )

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,getdate(),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,getdate(),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,getdate(),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,getdate(),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,getdate(),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,getdate(),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,getdate(),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,getdate(),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,getdate(),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,getdate(),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,getdate(),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,getdate(),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,getdate(),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,getdate(),1300,null,10);


select * from emp;


WITH SCOTT_EMP AS(
SELECT EMPNO,MGR,ENAME,0 T FROM EMP WHERE MGR IS NULL
UNION all
SELECT E.EMPNO,E.MGR,E.ENAME,M.T+1 FROM EMP E
INNER JOIN SCOTT_EMP M ON E.MGR=M.EMPNO
)
SELECT * FROM SCOTT_EMP;

Answer 1

In oracle, you can use start with connect by to achieve the same result, no need to use nested query

Preparation

CREATE TABLE EMP 
   (    EMPNO numeric(4,0), 
    ENAME VARCHAR(10 ), 
    JOB VARCHAR(9 ), 
    MGR numeric(4,0), 
    HIREDATE DATE, 
    SAL numeric(7,2), 
    COMM numeric(7,2), 
    DEPTNO numeric(2,0)
   );

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,sysdate,800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,sysdate,1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,sysdate,1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,sysdate,2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,sysdate,1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,sysdate,2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,sysdate,2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,sysdate,3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,sysdate,5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,sysdate,1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,sysdate,1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,sysdate,950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,sysdate,3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,sysdate,1300,null,10);

Query:

select empno, mgr, ename, level - 1 as T
  from emp
  start with mgr is null
  connect by prior empno = mgr;

Output:

| EMPNO |    MGR |  ENAME | T |
|-------|--------|--------|---|
|  7839 | (null) |   KING | 0 |
|  7566 |   7839 |  JONES | 1 |
|  7788 |   7566 |  SCOTT | 2 |
|  7876 |   7788 |  ADAMS | 3 |
|  7902 |   7566 |   FORD | 2 |
|  7369 |   7902 |  SMITH | 3 |
|  7698 |   7839 |  BLAKE | 1 |
|  7499 |   7698 |  ALLEN | 2 |
|  7521 |   7698 |   WARD | 2 |
|  7654 |   7698 | MARTIN | 2 |
|  7844 |   7698 | TURNER | 2 |
|  7900 |   7698 |  JAMES | 2 |
|  7782 |   7839 |  CLARK | 1 |
|  7934 |   7782 | MILLER | 2 |

Answer 2

You need to specify the columns in your CTE expression. In SQL Server this is just an optional property. In 11g you'll need to define them. Change your code to this:

WITH SCOTT_EMP(EMPNO,MGR,ENAME,T) AS(
    SELECT EMPNO,MGR,ENAME,0 T FROM EMP WHERE MGR IS NULL
    UNION all
    SELECT E.EMPNO,E.MGR,E.ENAME,M.T+1 FROM EMP E
    INNER JOIN SCOTT_EMP M ON E.MGR=M.EMPNO
)
SELECT * FROM SCOTT_EMP;