Saturday, May 31, 2008

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;

No comments:

Post a Comment