Skip to Main Content
  • Questions

  • Recursive with clause traversing the tree in reverse

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, steven.

Asked: October 01, 2015 - 8:26 pm UTC

Last updated: October 02, 2015 - 4:14 am UTC

Version: Oracle 12c Release 12.1.0.2

Viewed 1000+ times

You Asked

SELECT RPAD ('*', 2 * LEVEL, '*') || ename ename, empno
FROM scott.emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
/
--this works as connect by. I can not figure out how to do the reverse tree using
--recursive with. I want to work the tree backwards using recursive with clause.

-- TIA
-- Steven

and Connor said...

I'm not entirely sure what you mean by "reverse" tree. So I've put some examples of the recursive WITH below - they might help with what you are trying to achieve

SQL> with EACH_LEVEL (empno, name, mgr) as
  2  ( --
  3    -- start with
  4    --
  5    select empno, ename, mgr
  6    from   scott.emp
  7    where  mgr is null
  8    --
  9    -- connect by
 10    --
 11    union all
 12    select emp.empno, emp.ename, emp.mgr
 13    from   scott.emp, EACH_LEVEL
 14    where  emp.mgr = each_level.empno
 15  )
 16  select *
 17  from   each_level;

     EMPNO NAME              MGR
---------- ---------- ----------
      7839 KING
      7566 JONES            7839
      7698 BLAKE            7839
      7782 CLARK            7839
      7499 ALLEN            7698
      7521 WARD             7698
      7654 MARTIN           7698
      7788 SCOTT            7566
      7844 TURNER           7698
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782
      7369 SMITH            7902
      7876 ADAMS            7788

14 rows selected.

SQL> with EACH_LEVEL (empno, name, mgr, hier_level) as
  2  ( --
  3    -- start with
  4    --
  5    select empno, ename, mgr, 1 hier_level
  6    from   scott.emp
  7    where  mgr is null
  8    --
  9    -- connect by
 10    --
 11    union all
 12    select emp.empno, emp.ename, emp.mgr, hier_level+1
 13    from   scott.emp, EACH_LEVEL
 14    where  emp.mgr = each_level.empno
 15  )
 16  select *
 17  from   each_level;

     EMPNO NAME              MGR HIER_LEVEL
---------- ---------- ---------- ----------
      7839 KING                           1
      7566 JONES            7839          2
      7698 BLAKE            7839          2
      7782 CLARK            7839          2
      7499 ALLEN            7698          3
      7521 WARD             7698          3
      7654 MARTIN           7698          3
      7788 SCOTT            7566          3
      7844 TURNER           7698          3
      7900 JAMES            7698          3
      7902 FORD             7566          3
      7934 MILLER           7782          3
      7369 SMITH            7902          4
      7876 ADAMS            7788          4

14 rows selected.

SQL> with EACH_LEVEL (empno, name, mgr, hier_level, padding) as
  2  ( --
  3    -- start with
  4    --
  5    select empno, ename, mgr, 1 hier_level, '' padding
  6    from   scott.emp
  7    where  mgr is null
  8    --
  9    -- connect by
 10    --
 11    union all
 12    select emp.empno, emp.ename, emp.mgr, hier_level+1, '**'||padding
 13    from   scott.emp, EACH_LEVEL
 14    where  emp.mgr = each_level.empno
 15  )
 16  select *
 17  from   each_level;

     EMPNO NAME              MGR HIER_LEVEL PADDING
---------- ---------- ---------- ---------- ----------------------------------------
      7839 KING                           1
      7566 JONES            7839          2 **
      7698 BLAKE            7839          2 **
      7782 CLARK            7839          2 **
      7499 ALLEN            7698          3 ****
      7521 WARD             7698          3 ****
      7654 MARTIN           7698          3 ****
      7788 SCOTT            7566          3 ****
      7844 TURNER           7698          3 ****
      7900 JAMES            7698          3 ****
      7902 FORD             7566          3 ****
      7934 MILLER           7782          3 ****
      7369 SMITH            7902          4 ******
      7876 ADAMS            7788          4 ******

14 rows selected.

SQL> with EACH_LEVEL (empno, name, mgr, hier_level, padding) as
  2  ( --
  3    -- start with
  4    --
  5    select empno, ename, mgr, 1 hier_level, '' padding
  6    from   scott.emp
  7    where  mgr is null
  8    --
  9    -- connect by
 10    --
 11    union all
 12    select emp.empno, emp.ename, emp.mgr, hier_level+1, '**'||padding
 13    from   scott.emp, EACH_LEVEL
 14    where  emp.mgr = each_level.empno
 15  )
 16  SEARCH BREADTH FIRST BY EMPNO SET IDX
 17  select *
 18  from   each_level;

     EMPNO NAME              MGR HIER_LEVEL PADDING                                         IDX
---------- ---------- ---------- ---------- ---------------------------------------- ----------
      7839 KING                           1                                                   1
      7566 JONES            7839          2 **                                                2
      7698 BLAKE            7839          2 **                                                3
      7782 CLARK            7839          2 **                                                4
      7499 ALLEN            7698          3 ****                                              5
      7521 WARD             7698          3 ****                                              6
      7654 MARTIN           7698          3 ****                                              7
      7788 SCOTT            7566          3 ****                                              8
      7844 TURNER           7698          3 ****                                              9
      7900 JAMES            7698          3 ****                                             10
      7902 FORD             7566          3 ****                                             11
      7934 MILLER           7782          3 ****                                             12
      7369 SMITH            7902          4 ******                                           13
      7876 ADAMS            7788          4 ******                                           14

14 rows selected.

SQL> with EACH_LEVEL (empno, name, mgr, hier_level, padding) as
  2  ( --
  3    -- start with
  4    --
  5    select empno, ename, mgr, 1 hier_level, '' padding
  6    from   scott.emp
  7    where  mgr is null
  8    --
  9    -- connect by
 10    --
 11    union all
 12    select emp.empno, emp.ename, emp.mgr, hier_level+1, '**'||padding
 13    from   scott.emp, EACH_LEVEL
 14    where  emp.mgr = each_level.empno
 15  )
 16  SEARCH DEPTH FIRST BY EMPNO SET IDX
 17  select *
 18  from   each_level;

     EMPNO NAME              MGR HIER_LEVEL PADDING                                         IDX
---------- ---------- ---------- ---------- ---------------------------------------- ----------
      7839 KING                           1                                                   1
      7566 JONES            7839          2 **                                                2
      7788 SCOTT            7566          3 ****                                              3
      7876 ADAMS            7788          4 ******                                            4
      7902 FORD             7566          3 ****                                              5
      7369 SMITH            7902          4 ******                                            6
      7698 BLAKE            7839          2 **                                                7
      7499 ALLEN            7698          3 ****                                              8
      7521 WARD             7698          3 ****                                              9
      7654 MARTIN           7698          3 ****                                             10
      7844 TURNER           7698          3 ****                                             11
      7900 JAMES            7698          3 ****                                             12
      7782 CLARK            7839          2 **                                               13
      7934 MILLER           7782          3 ****                                             14

14 rows selected.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!

A reader, October 02, 2015 - 11:35 am UTC

I was wondering how to start with 'ADAMS' and end up back at 'King". I was trying to reverse the query from bottom to top instead of top to bottom using the recursive with clause.
Same results except top row would be 'ADAMS' and last row would be 'King'. Of course it is pretty easy to take the result and reverse it, but that would not be using the recursive with clause to walk the tree in reverse.

A reader, October 02, 2015 - 2:34 pm UTC

You just need to find the bottom of the tree (employees who are not managers) and reverse the connect by.

SELECT RPAD ('*', 2 * LEVEL, '*') || ename ename, empno
FROM scott.emp
START WITH empno not in (select mgr from scott.emp where mgr is not null)
CONNECT BY PRIOR mgr = empno
/

A reader, October 02, 2015 - 4:59 pm UTC 

  1  with data as
  2  (
  3     select 1 cid, null pid from dual union all
  4     select 2, 1 from dual union all
  5     select 3, 1 from dual union all
  6     select 4, 2 from dual union all
  7     select 5, 3 from dual
  8  ),
  9  walk_bottom_up(cid, pid, path) as
 10  (
 11     select cid, pid, to_char(cid)
 12     from data
 13     where cid not in (select pid from data where pid is not null)
 14     union all
 15     select d.cid, d.pid, path || '->' || to_char(d.cid)
 16     from data d, walk_bottom_up w
 17     where w.pid=d.cid
 18  )
 19  select * from walk_bottom_up
 20* order by path
 21  /

       CID        PID
---------- ----------
PATH
--------------------------------------------------------------------------------
         4          2
4

         2          1
4->2

         1
4->2->1


       CID        PID
---------- ----------
PATH
--------------------------------------------------------------------------------
         5          3
5

         3          1
5->3

         1
5->3->1


6 rows selected.

steven kladitis, October 02, 2015 - 6:32 pm UTC

WITH EACH_LEVEL (empno,
name,
mgr,
hier_level,
padding)
AS ( --
-- start with
--
SELECT empno,
ename,
mgr,
1 hier_level,
'' padding
FROM emp
WHERE empno NOT IN (SELECT mgr
FROM emp
WHERE mgr IS NOT NULL)
--
-- connect by
--
UNION ALL
SELECT emp.empno,
emp.ename,
emp.mgr,
hier_level + 1,
'**' || padding
FROM emp
JOIN EACH_LEVEL ON emp.empno = each_level.mgr)
SEARCH DEPTH FIRST BY EMPNO SET IDX
SELECT *
FROM each_level;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.