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;
Management Hierarchy
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). Hierarchy query
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. Hierarchy query
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 Hierarchy query
Figure 4

CONNECT_BY_ROOT

Back



Oracle registered trademark of Oracle Corporation.
    23694