How to Use Hierarchial Queries or equalent normal queries..

714761

I am using
OS - Windows
Oracle 10g R2

Im new to PL/SQL.

I actually need to find the hierarchy of employees in a company.

For Example..

base employee ->unit mgr ->Team mgr -> Business Analyst -> Delivery Chief.... goes on...

I need to actually find the emp_id of these hierarchy and put them in a table or display it using select statement.

In the above example one of the employee may report to multiple seniors.

For eg.

base employee may report to unit mgr1 unit mgr 2 or team mgr may report to multiple Business analysts.

I want to know how to get these employees hierarchy and put them in a table or just select them for dispalying using select statement.

Only two fields is needed

SELECT emp_id, sr_rep_to_emp
FROM emp_system_role;

emp_id is the employee id and sr_rep_to_emp is the id to whom employee is reporting to.

I dont know how to use Connect by statemts...to perform this.

With Regards
Suvin Prathab

Arun Kumar Gupta

You mentioned that

Suvin Prathab wrote:
base employee may report to unit mgr1 unit mgr 2 or team mgr may report to multiple Business analysts.

But you did not mention what needs to be displayed in this case.


Regards
Arun

MichaelS

I actually need to find the hierarchy of employees in a company.

you can start with

	 SELECT LEVEL, emp_id, sr_rep_to_emp
		FROM emp_system_role
CONNECT BY PRIOR emp_id = sr_rep_to_emp
START WITH sr_rep_to_emp IS NULL   --- assuming the highest level in the organisation has no entry for sr_rep_to_emp

ora_1978

Look at the below query

with emp as (
select 1 eno,'a' ename,2 mgr from dual union all
select 2 eno,'b' ename,3 mgr from dual union all
select 3 eno,'c' ename,null mgr from dual
)
select eno,ltrim(sys_connect_by_path((select ename from emp t where t.eno=r.mgr),','),',') from emp r connect by prior eno=mgr start with mgr is null order by r.eno

714761

Each employee has only one reporting head

emp_id sr_rep_to_emp
---------- ---------------------
a b a reports to b
b c b reports to c
c d c reports to d
d d is the boss

here d is the boss and a is the lower level employee.


Each employee has multiple reporting head

emp_id sr_rep_to_emp
---------- ---------------------
a b a reports to b ----a multiple reporting
a c a reports to c ----a multiple reporting
c d c reports to d
d e d reports to e
e d e reports to d ---- e multiple reporting
e f e reports to f ---- e multiple reporting
f f is the boss


this is wat the result i needed ....

714761

Each employee has only one reporting head

emp_id------sr_rep_to_emp
-------------------------------------
a---------------------b--------------a reports to b
b---------------------c--------------b reports to c
c---------------------d--------------c reports to d
d---------------------d is the boss

here d is the boss and a is the lower level employee.


Each employee has multiple reporting head

emp_id------sr_rep_to_emp
-------------------------------------
a--------------------- b-------------- a reports to b ----a multiple reporting
a---------------------c--------------a reports to c ----a multiple reporting
c---------------------d-------------- c reports to d
d---------------------e-------------- d reports to e
e---------------------d--------------e reports to d ---- e multiple reporting
e---------------------f --------------e reports to f ---- e multiple reporting
f------------------------------------- f is the boss


this is wat the result i needed ....

ravikumar.sv

with emp as (
select 1 eno,null mgr from dual union all
select 2 eno,1 mgr from dual union all
select 3 eno,1 mgr from dual union all
select 3 eno,2 mgr from dual 
)
select eno, eno||' reports to '||mgr FROM EMP CONNECT BY PRIOR ENO=MGR start with mgr=1

1 is manager to 2,3 is reporting to both 1 and 2...check the output

Ravi Kumar