Query Help

[kewldudehere]

Hi All,
I have a data in table as follows.

Employee Manager
b a
c b
d c

I should write a query that will get 'b','c','d' for 'a'.

[amitpatel66]

Your explanation is not clear to what you are looking at. Your sample data does not sync with your requirement. You want b,c,d for manager a but your sample data shows only one employee b for manager a. Please clarify on this.

And are you looking at displaying all the employees for a particular manager in a single record?

[Saii]

SELECT     lpad(' ',2*(level-1)) || to_char(employee)
      FROM <table_name>
START WITH manager = 'a'
CONNECT BY PRIOR employee = manager

Maybe this will help!!! though it will not result in single line result.

[kewldudehere]

hi saii,

this is exactly wat i want..thanks a lot.

[kewldudehere]

SELECT     lpad(' ',2*(level-1)) || to_char(employee)
      FROM <table_name>
START WITH manager = 'a'
CONNECT BY PRIOR employee = manager

Maybe this will help!!! though it will not result in single line result.

Hi saii..,

But i have some data where employee is same as manager i.e top level person reports to himself. so i i use the above query i am getting an error 'connect by loop in user data'. I want to get the top level manager too

[debasisdas]

try using this sample code
[code=oracle]
SELECT LEVEL,LPAD(' ',2*(LEVEL-1))|| ENAME "EMPLOYEE", EMPNO,MGR FROM EMP START WITH EMPNO=7839
CONNECT BY NOCYCLE PRIOR EMPNO=MGR;
[/code]

[kewldudehere]

Hi ,

I am getting error at 'Prior' as 'ORA-00920: invalid relational operator
' .
I am using Oracle 9i.