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. |
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; |
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; |
Hierarchy Analysis
Connect_By_Root
BOM Analysis
ORA-01436
ORA-30004
DB Technology