Re: Pure SQL transitive closure with LEVEL

Good news: Oracle 9i can do joins along with CONNECT BY. Following will do the job.

SELECT boss.empno bossno , e1.empno empno, level-1 distance  FROM emp boss, emp e1

       WHERE    LEVEL  >1
       START WITH e1.empno= boss.empno
       CONNECT BY PRIOR e1.empno = e1.mgr AND PRIOR boss.empno=boss.empno

Regards
Sergey Levin
gerg_at_ncom.ru

danielroy10_at_hotmail.com (Daniel Roy) wrote in message news:<1b061893.0212010843.5aea46d6_at_posting.google.com>...
> 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 15 2002 - 18:47:53 CET