The diagram beside, is a generic corporate management hierarchy where 100 represents the President, 101 the VP, 102 the Director and so on. The query (XMLAGG) below shows the manager-employee relationship in summary. The management hierarchy can be complex and without a diagram, it is simpler to analyze using a query shown below (query 2). As shown below, 100 has no manager, 101 has manager 100 and so on. As we traverse down the hierarchy, the level keeps on going higher. In this example there are four levels from top to bottom. This query syntax (query 2) can be used to know the reporting structure in HR systems, or prerequisites needed for a course etc. Management Hierarchy
Query 1: Manager-Employee Summary
SELECT q_tab.mgr_no, COUNT(*) emp_count,
Rtrim(Xmlagg(Xmlelement(e,q_tab.emp_no,'; ').Extract('//text()')
Order By Q_tab.emp_no),'; ') AS employee_list_4_MGR
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
) q_tab
GROUP BY q_tab.mgr_no;
Management Hierarchy Summary

Query 2: Manager-Employee Hierarchy Analysis
SELECT
  q_tab.emp_no, q_tab.mgr_no, 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
) q_tab
Start With q_tab.emp_no = &i_emp_no
Connect By Prior q_tab.mgr_no = q_tab.emp_no;
Management Hierarchy Analysis

Back



Oracle registered trademark of Oracle Corporation.
    7602