Saturday, June 25, 2011

CONNECT BY PRIOR and ORDER SIBLINGS BY

Tested on an Oracle 9 database. Connect by prior was available in Oracle 8. It allows you to show recursive relationships such as managers and their employees or assemblies and their sub assemblies in a top-down fashion. The order by siblings clause is new. It lets you sort the siblings at each level. You can see the difference in the example below where the same data is displayed twice. The first time the siblings are not sorted whereas the second time they are:
  
SQL> col Manager format a15
SQL> col Employee format a15
SQL> create table emp
  2  (ename varchar2(10),
  3   mgr   varchar2(10));
 
Table created.
 
SQL> insert into emp values('Russell','King');
 
1 row created.
 
SQL> insert into emp values('Smith','King');
 
1 row created.
 
SQL> insert into emp values('Jones','King');
 
1 row created.
 
SQL> insert into emp values('Morrison','Russell');
 
1 row created.
 
SQL> insert into emp values('Matthews','Russell');
 
1 row created.
 
SQL> insert into emp values('Pearson','Russell');
 
1 row created.
 
SQL> insert into emp values('Williams','Smith');
 
1 row created.
 
SQL> insert into emp values('Brown','Smith');
 
1 row created.
 
SQL> insert into emp values('Fielder','Brown');
 
1 row created.
 
SQL> insert into emp values('Moir','Fielder');
 
1 row created.
 
SQL> select lpad(' ',level-1)||mgr Manager,
  2  lpad(' ',level-1)||ename Employee
  3  from emp
  4  start with mgr = 'King'
  5  connect by prior ename = mgr
  6  /
 
MANAGER         EMPLOYEE
--------------- ---------------
King            Russell
 Russell         Morrison
 Russell         Matthews
 Russell         Pearson
King            Smith
 Smith           Williams
 Smith           Brown
  Brown           Fielder
   Fielder         Moir
King            Jones
 
10 rows selected.
 
SQL> select lpad(' ',level-1)||mgr Manager,
  2  lpad(' ',level-1)||ename Employee
  3  from emp
  4  start with mgr = 'King'
  5  connect by prior ename = mgr
  6  order siblings by ename
  7  /
 
MANAGER         EMPLOYEE
--------------- ---------------
King            Jones
King            Russell
 Russell         Matthews
 Russell         Morrison
 Russell         Pearson
King            Smith
 Smith           Brown
  Brown           Fielder
   Fielder         Moir
 Smith           Williams
 
10 rows selected.
 
SQL>

No comments: