Re: Tree Walk statements

From: Peter Schneider <pschneider_at_knuut.de>
Date: Fri, 08 Jan 1999 11:39:17 GMT
Message-ID: <3695ead0.64427719_at_personalnews.de.uu.net>

jstopforth_at_icon.co.za (Justin Stopforth) wrote:

>Hi
>
>I'm having trouble with a tree walk statement and if anyone could give
>me a hand I would appreciate it.
>
>Here's the deal:
>
>Table name: assignments
>Field1: employee (pk)
>Field2: manager (also an employee)
>Top level managers have themselves as managers

Hi Justin,

in this case, a plain treewalk query won't work, as you will get the error 'ORA-01436: CONNECT BY loop in user data'. A treewalk query may hit every row only once, i.e. the data structure it represents must be cycle-free. But you could use a connect-by clause with a decode statement, like this (this is the usual emp table in the demo schema SCOTT): SQL> r
  1 select empno, ename, mgr
  2 from emp
  3 connect by prior empno = mgr
  4* start with ename = 'KING'

    EMPNO ENAME MGR
--------- ---------- ---------

     7839 KING
     7566 JONES           7839
     7788 SCOTT           7566
     7876 ADAMS           7788
     7902 FORD            7566
     7369 SMITH           7902
     7698 BLAKE           7839
     7499 ALLEN           7698
     7521 WARD            7698
     7654 MARTIN          7698
     7844 TURNER          7698
     7900 JAMES           7698
     7782 CLARK           7839
     7934 MILLER          7782

14 rows selected.

SQL> update emp
  2 set mgr = empno
  3 where ename = 'KING';

1 row updated.

SQL> select empno, ename, mgr
  2 from emp
  3 connect by prior empno = mgr
  4 start with ename = 'KING';

    EMPNO ENAME MGR
--------- ---------- ---------

     7839 KING            7839
     7566 JONES           7839
     7788 SCOTT           7566
     7876 ADAMS           7788
     7902 FORD            7566
     7369 SMITH           7902
     7698 BLAKE           7839
     7499 ALLEN           7698
     7521 WARD            7698
     7654 MARTIN          7698
     7844 TURNER          7698
     7900 JAMES           7698
     7782 CLARK           7839
     7934 MILLER          7782
     7839 KING            7839

ERROR:
ORA-01436: CONNECT BY loop in user data

14 rows selected.

SQL> r
  1 select empno, ename, mgr
  2 from emp
  3 connect by prior empno = decode(mgr, empno, null, mgr)   4* start with ename = 'KING'

    EMPNO ENAME MGR
--------- ---------- ---------

     7839 KING            7839
     7566 JONES           7839
     7788 SCOTT           7566
     7876 ADAMS           7788
     7902 FORD            7566
     7369 SMITH           7902
     7698 BLAKE           7839
     7499 ALLEN           7698
     7521 WARD            7698
     7654 MARTIN          7698
     7844 TURNER          7698
     7900 JAMES           7698
     7782 CLARK           7839
     7934 MILLER          7782

14 rows selected.

Please note that on this table, the hierarchy is implemented with a recursive foreign key (aka pig's ear). With the information you gave about your table, it is not quite clear to me how you implemented this, so if all this makes no sense to you, please post more details about keys and relationships in your design. Is 'assignments' an intersection table, say between employees and projects or such ?

HTH,
Peter

-- 
Peter Schneider
pschneider_at_knuut.de

Received on Fri Jan 08 1999 - 12:39:17 CET