Home » SQL & PL/SQL » SQL & PL/SQL » problem with connect by prior
problem with connect by prior [message #165691] Fri, 31 March 2006 08:55 Go to next message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
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!
Re: problem with connect by prior [message #189583 is a reply to message #165691] Fri, 25 August 2006 03:07 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Basically, You cannot do this using connect by, as You would get a loop, as ABCD is the manager of ABCD, who is the manager of ABCD, who is the manager of ABCD, who is the manager of ABCD, who is the manager of ABCD, who is the manager of ABCD, who etc.

If You need this, use a UNION ALL and then select the employee name twice.

Br
Kim
Previous Topic: script validation against tables
Next Topic: need help in format
Goto Forum:
  


Current Time: Sun Dec 04 08:35:54 CST 2016

Total time taken to generate the page: 0.04817 seconds