Wednesday, December 16, 2009

Showing Peers in a Connect by

I built a system internally a while ago and one of the most popular pages was one that showed a tree of data. We even got accused of caching the page because it was too dynamic and fast.  Here's what I did and what the output ended up like.

In SQL Developer:



 
In Application Express:  The leading   gets highlighted as part of the link but I'm sure there CSS wizards that could fix that.


And try it here


with q as (  select level lvl,empno
      from emp e
      connect by prior empno  = mgr
      start with empno = 7839   ) 
select SYS_CONNECT_BY_PATH(sub.ename,'/'),LPAD(' ',((18*(level-1))+1),' ') ||sub.ename,sub.empno,sal,comm,d.dname
from dept d,(
    select e.* 
    from emp e
    connect by prior mgr=empno
    start with empno = 7566
union 
    select e.* 
    from emp e
    where level = (select lvl from q where empno = 7566 ) 
    connect by prior empno = mgr
    start with empno = 7839
 union 
    select e.* 
    from emp e
    where mgr = 7566
  ) sub    
where d.deptno = sub.deptno  
connect by prior empno  = mgr
start with empno = 7839  


Once it breaks down it's quite simple.  The inner unions are
1) The parents of the interesting record
2) The peers of the interesting record
3) The children of the interesting record

Then put the outer connect by and you have what I got.