Forum Stats

  • 3,817,325 Users
  • 2,259,313 Discussions
  • 7,893,749 Comments

Discussions

How to Use Hierarchial Queries or equalent normal queries..

714761
714761 Member Posts: 18
edited Aug 12, 2009 1:58AM in SQL & PL/SQL
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
Tagged:

Answers

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 1,002 Gold Badge
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    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
    ora_1978 Member Posts: 497 Bronze Badge
    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
    714761 Member Posts: 18
    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
    714761 Member Posts: 18
    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
    ravikumar.sv Member Posts: 1,072
    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
This discussion has been closed.