There is a work around:
Consider this Hierarchy:
PHP Code:
. Tamil .
/ | .
/ | .
/ | .
Tom Raj .
/ \ / | | .
/ \ / | | .
Scott Bush Veera Appu Mike
SQL> desc t1
Name Null? Type
----------------- -------- ------------
NAME CHAR(10)
LFT NUMBER(38)
RGT NUMBER(38)
SQL> select * from t1 ;
NAME LFT RGT
---------- ---------- ----------
Tamil 1 16
Tom 2 7
Scott 3 4
Bush 5 6
Raj 8 15
Veera 9 10
Appu 11 12
Mike 13 14
To list employees reporting to Tom, run the query:
SQL> get x1
1 select a.name "Boss", b.name "Employee"
2 from t1 a, t1 b
3 where a.name = 'Tom'
4 and b.lft > a.lft
5* and b.lft < a.rgt
SQL> /
Boss Employee
---------- ----------
Tom Scott
Tom Bush
To list employees reporting to Tamil,
SQL> get x1
1 select a.name "Boss", b.name "Employee"
2 from t1 a, t1 b
3 where a.name = 'Tamil'
4 and b.lft > a.lft
5* and b.lft < a.rgt
SQL> /
Boss Employee
---------- ----------
Tamil Tom
Tamil Scott
Tamil Bush
Tamil Raj
Tamil Veera
Tamil Appu
Tamil Mike
7 rows selected.
The difficult part of the design is assigning left number and right number, and maintaining the hierarchy. The left and right numbers of each node contain the left and right numbers of the ancestors of that node. For assigning the left and right numbers, use a procedure/function.
Tamil