SELECT q_tab.emp_no, q_tab.Mgr_No,   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 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 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. 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 Figure 4

Oracle registered trademark of Oracle Corporation.
627