Connect By

Show the hierarchy of data using 'Connect By' statements. 'Order Siblings' is new in 9i.

Example 1:

Set Pagesize 60
Column Empnm Format A20  Heading EmpNm
Column Mgr   Format 9999 Heading Mgr
Column Empno Format 9999 Heading Emp#

Ttitle Left 'Example 1:' skip 2

Select Empno,
            Rpad(
                   Lpad(' ',3*(Level-1)) /* indention based on level*/
                 ||Level||'. '           /* location on food chain */
                 ||Ename                 /* employee's name */
             ,20
             ,'. ')         /* Right pad with periods and spaces*/
            As
      Empnm,
      Mgr
 From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order Siblings By Ename;

---

Example 2:

Column Enames  Format A14
Column Sortord Format A23

Ttitle Left 'Example 2:' skip 2

Select            Lpad (' ', 3*(Level-1))||Ename
                As
      Enames,     Sys_Connect_By_Path(Ename, '/' )
                As
      Sortord
 From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order By Sortord;