CODEWITHSUNDEEP
oracle-databaseplsqlhierarchical-data
jayz
jayz

Reputation: 401

Get the subordinate list of values from a table where the employee and supervisor is mentioned

I have a dataset like below

employee Supervisor
A B
B C
D E
F B
G F

In this data set I want to access each employee one by one when I give a supervisor as input. for an example if I pas B as the input first get A and go B like that till the end and then comeback to F, then go to G. I want to get them in a order so that I have to apply some data of the top most supervisor to the whole data set. Thank you in advance.

Upvotes: 0

Views: 133

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

This is how I understood the question; the first column (employee) shows all employees who are subordinate to the supervisor (c in this example), while path shows ... well, the path that leads from chosen supervisor to that employee.

SQL> with dataset (employee, supervisor) as
  2    (select 'a', 'b' from dual union all
  3     select 'b', 'c' from dual union all
  4     select 'd', 'e' from dual union all
  5     select 'f', 'b' from dual union all
  6     select 'g', 'f' from dual
  7    )
  8  select
  9    employee,
 10    supervisor,
 11    ltrim(sys_connect_by_path(employee, ' - '), ' - ') as path
 12  from dataset
 13  start with supervisor = 'c'                 --> this is the supervisor you're interested in
 14  connect by supervisor = prior employee;

E S PATH
- - --------------------
b c b
a b b - a
f b b - f
g f b - f - g

SQL>

If you want to "loop" through employees, then you'll need PL/SQL and ... well, a loop. Something like this:

SQL> set serveroutput on
SQL> declare
  2    l_supervisor varchar2(1) := 'c';
  3  begin
  4    for cur_r in
  5      (with dataset (employee, supervisor) as
  6          (select 'a', 'b' from dual union all
  7           select 'b', 'c' from dual union all
  8           select 'd', 'e' from dual union all
  9           select 'f', 'b' from dual union all
 10           select 'g', 'f' from dual
 11          )
 12        select
 13          employee,
 14          supervisor,
 15          ltrim(sys_connect_by_path(employee, ' - '), ' - ') as path
 16        from dataset
 17        start with supervisor = l_supervisor
 18        connect by supervisor = prior employee
 19      )
 20    loop
 21      -- you'd do something with this employee; I'm just displaying it
 22      dbms_output.put_line('Employee = ' || cur_r.employee);
 23    end loop;
 24  end;
 25  /
Employee = b
Employee = a
Employee = f
Employee = g

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions