get all hierarchy data

[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

[amitpatel66]

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

[salesmr]

Maybe I wasn't clear in what I was asking. Ultimately I want something similar to the "sys_connect_by_path" (so that I can see ALL of the parents to a child), but I have 8 rather than 9i so it is not an option for me to use this function. I'm trying to reproduce the output though. Is this something that you can help with?

Thanks, Michelle