Re: Pure SQL transitive closure with LEVEL

"Damjan S. Vujnovic" <damjan_at_NOSPAMgaleb.etf.bg.ac.yu> wrote in message news:<arvgjs$ktm$1_at_news.etf.bg.ac.yu>...
> > I need pure SQL code to compute transitive closure from adjacency
> > matrix, say table X(ParentID, ChildID). I managed to compute all
> > ancestor/predecessor pairs, but couldn't
> > find the way to get a distance (level) between them in same query.
> >
> > Any ideas?
>
> Hint: CONNECT BY PRIOR
>
> Regards,
> Damjan S. Vujnovic
>
> University of Belgrade
> School of Electrical Engineering
> Department of Computer Engineering & Informatics
> Belgrade, Yugoslavia
>
> http://galeb.etf.bg.ac.yu/~damjan/

OK, I'll try to be more specific.
Let me take EMP table from ORACLE scott/tiger demo user as an example.

  SELECT LEVEL, e1.* FROM emp e1

       WHERE 1=1   
       START WITH e1.mgr IS NULL 
       CONNECT BY e1.MGR= PRIOR e1.empno  ;

builds the tree:

1 7839 KING   PRESIDENT      17.11.81 00:00 5000      10
2 7566 JONES  MANAGER   7839 02.04.81 00:00 2975      20
3 7788 SCOTT  ANALYST   7566 19.04.87 00:00 3000      20
4 7876 ADAMS  CLERK     7788 23.05.87 00:00 1100      20

What I need is to list all predecessor/ancestor pairs with a distance: 7839 7566 1
7839 7788 2
7839 7876 3
...
7566 7876 2
...
Distance for all pairs IS my problem.

Regards
Serg Levin
gerg_at_ncom.ru Received on Wed Nov 27 2002 - 14:30:47 CET