Re: Pure SQL transitive closure with LEVEL

I found a way to display also the number of levels of distance, but it's not very elegant. Anyone has an idea how to improve this?

SQL> select e1.empno "Manager",e2.empno "Employee",1 "Levels" from emp e1,emp e2
  2 where e1.empno <> e2.empno
  3 and exists (select level from emp e3   4 where e3.empno = e2.empno
  5 and level = 1
  6 start with e3.mgr = e1.empno
  7 connect by e3.mgr = prior e3.empno)   8 union all
  9 select e1.empno "Manager",e2.empno "Employee",2 "Levels" from emp e1,emp e2
 10 where e1.empno <> e2.empno
 11 and exists (select level from emp e3  12 where e3.empno = e2.empno
 13 and level = 2
 14 start with e3.mgr = e1.empno
 15 connect by e3.mgr = prior e3.empno)  16 union all
 17 select e1.empno "Manager",e2.empno "Employee",3 "Levels" from emp e1,emp e2
 18 where e1.empno <> e2.empno
 19 and exists (select level from emp e3  20 where e3.empno = e2.empno
 21 and level = 3
 22 start with e3.mgr = e1.empno
 23 connect by e3.mgr = prior e3.empno)  24 order by "Levels" desc;

   Manager Employee Levels
---------- ---------- ----------

      7839       7369          3
      7839       7876          3
      7566       7369          2
      7839       7499          2
      7839       7521          2
      7839       7844          2
      7839       7788          2
      7839       7654          2
      7566       7876          2
      7839       7902          2
      7839       7934          2
      7839       7900          2
      7902       7369          1
      7698       7499          1
      7698       7521          1
      7839       7566          1
      7698       7654          1
      7839       7698          1
      7839       7782          1
      7566       7788          1
      7698       7844          1
      7788       7876          1
      7698       7900          1
      7566       7902          1
      7782       7934          1

25 rows selected.

Daniel Received on Sun Dec 01 2002 - 17:43:35 CET