Quote:
Originally Posted by salesmr
I have created a query using the connect by prior syntax to get a list of all employees and their hierarchy, but am trying to store this information in a table starting with the employee and storing their management information all the way up to our president.
For example,
Employee ID -> Direct Supervisor ID -> Manager ID -> Senior Manager ID -> Director ID -> VP ID -> Pres ID
for each employee. Obviously some will have more levels than other employees, but need to have this dynamic.
Currently, I have this query, but cannot figure out how to get data from hierarchy to table format as discussed above:
SELECT lpad(' ', (level - 1) * 4) || emp_name, level
from hr_employees
where emp_status = 'Active'
CONNECT BY manager_id = prior emp_id
START WITH emp_id = '123456'
Please help....thanks, Michelle
If you want to insert the data returned by the above select query you can use the following logic to insert into another table:
-
-
INSERT INTO tab1 (SELECT lpad(' ', (level - 1) * 4) || emp_name, level
-
from hr_employees
-
where emp_status = 'Active'
-
CONNECT BY manager_id = prior emp_id
-
START WITH emp_id = '123456')
-
Make sure the table tab1 have 2 columns to insert the data returned by SELECT query
