SELECT q_tab.emp_no, q_tab.Mgr_No,   CONNECT_BY_ROOT q_tab.emp_no root_emp,   CONNECT_BY_ROOT q_tab.mgr_no root_mgr,   SUBSTR(SYS_CONNECT_BY_PATH(q_tab.emp_no,' <-- '),6) management_hierarchy,   CONNECT_BY_ISLEAF is_leaf, LEVEL FROM   (SELECT 1 no, 1001 emp_no, 102 mgr_no FROM DUAL UNION    SELECT 2 no, 1002 emp_no, 102 mgr_no FROM DUAL UNION    SELECT 3 no, 1003 emp_no, 102 mgr_no FROM DUAL UNION    SELECT 4 no, 1004 emp_no, 1001 mgr_no FROM DUAL UNION    SELECT 5 no, 1005 emp_no, 1001 mgr_no FROM DUAL UNION    SELECT 6 no, 1006 emp_no, 1002 mgr_no FROM DUAL UNION    SELECT 7 no, 1007 emp_no, 1002 mgr_no FROM DUAL UNION    SELECT 8 no, 1008 emp_no, 1003 mgr_no FROM DUAL UNION    SELECT 9 no, 1009 emp_no, 1003 mgr_no FROM DUAL UNION    Select 10 No, 101 Emp_No, 100 Mgr_No From Dual Union    Select 11 No, 102 Emp_No, 101 Mgr_No From Dual Union    Select 12 No, 100 Emp_No, null Mgr_No From Dual) Q_Tab START WITH q_tab.emp_no = &i_emp CONNECT BY NOCYCLE PRIOR q_tab.emp_no = q_tab.mgr_no ORDER SIBLINGS BY q_tab.emp_no;

Figure 1

The management hierarchy can be analyzed from any level by changing the value of START WITH. This is the output (Figure 2) when starting with the manager ID of 100. This syntax shows that, by starting at 100 there are five levels (four below 100). Also 100 has no manager representing President/CEO. The ROOT manager is NULL. As the starting value of employee ID is 100, it is the ROOT employee. The leaf represents the lowest level (0 being false and 1 being true).

Figure 2

When starting with 102, there are three levels and two levels below 102. This is the output (Figure 3) when starting with manager ID of 102. The ROOT employee is 102 and ROOT manager points to manager of 102, which is 101

Figure 3

When starting with 1001, there are two levels and only one level below 1001. This is the output (Figure 4) when starting with manager ID of 1001. The ROOT employee is 1001 and ROOT manager points to manager of 1001, which is 102.

Figure 4

Back