CODEWITHSUNDEEP
sqloracle-database
shubham
shubham

Reputation: 99

how to retrieve employee hierarchy without using connect by clause in oracle

create table test 
(
emp_id number ,
emp_name varchar2(10),
mgr_id number ) ;

insert into test values(1,'amar',null);

insert into test values(2,'ram',1);

insert into test values(3,'raju',2);

insert into test values(4,'vinod',3);

insert into test values(5,'amar1',null);

insert into test values(6,'ram1',5);

insert into test values(7,'raju1',6);

insert into test values(8,'vinod1',7);

now i want hierarchy of top level employee who has no manager . required comma seperated employee list

expected output :

| emp_id | emp_id_under_manager |

|1      |    2,3,4 |

|5      |    6,7,8 |

employee id 1 and 5 has no manager hence displayed only that 2 employees hierarchy

WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS
(
  SELECT e.emp_id, e.emp_name, CAST(e.emp_id AS CHAR(200))  
    FROM test e 
    WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, CONCAT(ep.path, ',', e.emp_id)
    FROM employee_paths ep JOIN test  e
      ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;

getting error like missing keyword

Upvotes: 0

Views: 528

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

getting error like missing keyword

There isn't a RECURSIVE keyword; a CTE is made recursive by referring to itself in a union. The concat() function only takes two arguments in Oracle too, so you would need to nest calls or more simply use the concatenation operator. Casting the manager ID to char(200) also means that is padded with spaces, so when you concatenate you have a big gap; you can use to_char() instead of cast():

WITH employee_paths (emp_id, emp_name, path) AS
(
  SELECT e.emp_id, e.emp_name, to_char(e.emp_id)
    FROM test e 
    WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, ep.path || ',' || e.emp_id
    FROM employee_paths ep JOIN test  e
      ON ep.emp_id = e.mgr_id
)
SELECT * FROM employee_paths ORDER BY path;

That still doesn't give you the output you want though...

EMP_ID EMP_NAME PATH   
------ -------- -------
     1 amar     1      
     2 ram      1,2    
     3 raju     1,2,3  
     4 vinod    1,2,3,4
     5 amar1    5      
     6 ram1     5,6    
     7 raju1    5,6,7  
     8 vinod1   5,6,7,8

If you just get the child ID in the recursive member you can use listagg() to get the comma-separated list:

with rcte (root_id, emp_id) as (
  select emp_id, emp_id
  from test
  where mgr_id is null
  union all
  select root_id, t.emp_id
  from rcte r
  join test t on t.mgr_id = r.emp_id
)
select root_id as emp_id,
  listagg(emp_id, ',') within group (order by emp_id) as emp_id_under_manager
from rcte
where emp_id != root_id
group by root_id

which with your sample data gets:

EMP_ID EMP_ID_UNDER_MANAGER
------ --------------------
     1 2,3,4
     5 6,7,8

db<>fiddle showing the recursive CTE with all output, then filtered and aggregated as above; and also showing the hierarchical query equivalent for info, and I've included your version and the corrected version (that doesn't error but gives wrong results, both with cast() and to_char()).

Upvotes: 2

Related Questions