Recursive Subquery Factoring (With Clause) Employee Manager Hierarchy

OLD 10g STYLE CONNECT BY PRIOR

select
    lpad(ename,(level*4)+length(ename),'-') data
    empno eid,
    ename,
    level lvl
from
    scott.emp
connect by prior
    empno=mgr
start with
    empno=7839


NEW ORACLE 11g RECURSIVE SUBQUERY FACTORING

with myemp(eid, ename, lvl) as
(
    select empno, ename, 1 as lvl from scott.emp where mgr is null
    union all
    select empno, e.ename, lvl+1 from scott.emp e, myemp m where e.mgr=m.eid
) search depth first by eid set seq
select lpad(ename,(lvl*4)+length(ename),'-') data, eid, ename, lvl from myemp order by seq

#Alternatively you can have siblings processed prior to children. Change DEPTH to BREADTH

OUTPUT

   

More Examples
- Recursive Subquery Factoring Examples: Factorial and Power
- Recursive Subquery Factoring Examples: Fibonacci

Related Links
- SQL Interview Question Answers
- Oracle SQL Puzzles
- With Clause: Subquery Factoring