 <p>In oracle, you can use <code>start with connect by</code> to achieve the same result, no need to use nested query</p> <p>Preparation</p> <pre><code>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); </code></pre> <p>Query:</p> <pre><code>select empno, mgr, ename, level - 1 as T from emp start with mgr is null connect by prior empno = mgr; </code></pre> <p>Output:</p> <pre><code>| 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 | </code></pre>