problem with connect by prior
Hi,
I've table TAB_MGR:
EMPLOYEE................MANAGER
ABCD...................ABC
ABC.....................AB
AB......................A
WAXXY..Y...............WAXX
WAXX...................WA
WA.....................W
I tried this query:
select a.EMPLOYEE, b.MANAGER
from
(select EMPLOYEE, MANAGER, rownum-level rl, level lv
from TAB_MGR connect by prior MANAGER = EMPLOYEE) a,
(select EMPLOYEE, MANAGER, rownum-level rl, level lv
from TAB_MGR connect by prior MANAGER = EMPLOYEE) b
where a.lv=1 and a.rl=b.rl;
output is:
EMPLOYEE....................MANAGER
ABCD........................ABC
ABCD........................AB
ABCD........................A
ABC.........................AB
ABC.........................A
AB..........................A
WAXXYY.....................WAXX
WAXXYY.....................WA
WAXXYY.....................W
WAXX.......................WA
WAXX.......................W
WA.........................W
but I'd like to get also the level 1 of employee = MANAGER
In my case I'd like to get this output:
EMPLOYEE........................MANAGER
ABCD..........................ABCD
ABCD..........................ABC
ABCD..........................AB
ABCD..........................A
ABC...........................ABC
ABC...........................AB
ABC...........................A
AB............................AB
AB............................A
WAXXYY........................WAXXYY
WAXXYY........................WAXX
WAXXYY........................WA
WAXXYY........................W
WAXX..........................WAXX
WAXX..........................WA
WAXX..........................W
WA............................WA
WA............................W
in my query lacks:
EMPLOYEE........................MANAGER
ABCD..........................ABCD
ABC...........................ABC
AB............................AB
WAXXYY........................WAXXYY
WAXX..........................WAXX
WA............................WA
How can I get also this record in my query??
Thanks!
