Oracle provides following pseudo columns to deal with hierarchical queries.
- LEVEL : The position in the hierarchy of the current row in relation to the root node.
- CONNECT_BY_ISLEAF : Indicates if the current row is a leaf node.
- CONNECT_BY_ISCYCLE : Indicates which record is responsible for the cycle.
Q : Get the hierarchical details of employees with root identified as the name of grade 5 employee?
Ans:
SELECT Ename, Empno, Job, Mgr, Sal
FROM Emp
START WITH Ename = (
SELECT Ename
FROM Emp, SalGrade
WHERE Emp.Sal BETWEEN SalGrade.LoSal
AND SalGrade.HiSal AND SalGrade.Grade = 5
)
CONNECT BY PRIOR Empno = Mgr;
ENAME EMPNO JOB MGR SAL
---------- ---------- --------- ---------- ----------
KING 7839 PRESIDENT 5000
JONES 7566 MANAGER 7839 2975
SCOTT 7788 ANALYST 7566 3000
ADAMS 7876 CLERK 7788 1100
FORD 7902 ANALYST 7566 3000
SMITH 7369 CLERK 7902 800
BLAKE 7698 MANAGER 7839 2850
ALLEN 7499 SALESMAN 7698 1600
WARD 7521 SALESMAN 7698 1250
MARTIN 7654 SALESMAN 7698 1250
TURNER 7844 SALESMAN 7698 1500
JAMES 7900 CLERK 7698 950
CLARK 7782 MANAGER 7839 2450
MILLER 7934 CLERK 7782 1300
14 rows selected.
Oracle provides LEVEL pseudo column to use in a hierarchical query to identify the position in the hierarchy of the current row with respect to the root node.
SELECT LEVEL, Ename, Empno, Job, Mgr, Sal
FROM Emp
START WITH Ename = (
SELECT Ename
FROM Emp, SalGrade
WHERE Emp.Sal BETWEEN SalGrade.LoSal
AND SalGrade.HiSal AND SalGrade.Grade = 5
)
CONNECT BY PRIOR Empno = Mgr;
LEVEL ENAME EMPNO JOB MGR SAL
---------- ---------- ---------- --------- ---------- ----------
1 KING 7839 PRESIDENT 5000
2 JONES 7566 MANAGER 7839 2975
3 SCOTT 7788 ANALYST 7566 3000
4 ADAMS 7876 CLERK 7788 1100
3 FORD 7902 ANALYST 7566 3000
4 SMITH 7369 CLERK 7902 800
2 BLAKE 7698 MANAGER 7839 2850
3 ALLEN 7499 SALESMAN 7698 1600
3 WARD 7521 SALESMAN 7698 1250
3 MARTIN 7654 SALESMAN 7698 1250
3 TURNER 7844 SALESMAN 7698 1500
3 JAMES 7900 CLERK 7698 950
2 CLARK 7782 MANAGER 7839 2450
3 MILLER 7934 CLERK 7782 1300
14 rows selected.
LEVEL with LPAD/RPAD function can make your hierarchical output look like actual hierarchy.
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
Mgr
FROM Emp
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL MGR
------------------------- ---------- --------- ---------- ----------
KING 7839 PRESIDENT 5000
JONES 7566 MANAGER 2975 7839
SCOTT 7788 ANALYST 3000 7566
ADAMS 7876 CLERK 1100 7788
FORD 7902 ANALYST 3000 7566
SMITH 7369 CLERK 800 7902
BLAKE 7698 MANAGER 2850 7839
ALLEN 7499 SALESMAN 1600 7698
WARD 7521 SALESMAN 1250 7698
MARTIN 7654 SALESMAN 1250 7698
TURNER 7844 SALESMAN 1500 7698
JAMES 7900 CLERK 950 7698
CLARK 7782 MANAGER 2450 7839
MILLER 7934 CLERK 1300 7782
14 rows selected.
Q : Get all the top level employees of “ADAMS”?
Ans:
SELECT LPAD(Ename, length(Ename) + 2*LEVEL-1) Ename,
Empno,
Mgr,
Job
FROM Emp
START WITH Ename = 'ADAMS'
CONNECT BY Empno = PRIOR Mgr;
ENAME EMPNO MGR JOB
-------------------- ---------- ---------- ---------
ADAMS 7876 7788 CLERK
SCOTT 7788 7566 ANALYST
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
Q : Get all bottom level job categories for MANAGER in each department?
Ans:
SELECT
LPAD(Job, length(Job) + 2*LEVEL-1) Job,
Deptno
FROM Emp
START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr;
JOB DEPTNO
-------------------- ----------
MANAGER 20
ANALYST 20
CLERK 20
ANALYST 20
CLERK 20
MANAGER 30
SALESMAN 30
SALESMAN 30
SALESMAN 30
SALESMAN 30
CLERK 30
MANAGER 10
CLERK 10
13 rows selected.
Q : Get all top level job categories for ANALYST in each department?
Ans:
SELECT
LPAD(Job, length(Job) + 2*LEVEL-1) Job,
Deptno
FROM Emp
START WITH Job = 'ANALYST'
CONNECT BY Empno = PRIOR Mgr;
JOB DEPTNO
-------------------- ----------
ANALYST 20
MANAGER 20
PRESIDENT 10
ANALYST 20
MANAGER 20
PRESIDENT 10
6 rows selected.
Q : Get the employees who works within two levels down from president?
Ans:
SELECT
LEVEL,
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
Mgr
FROM Emp
WHERE LEVEL IN(2,3)
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
LEVEL ENAME EMPNO JOB SAL MGR
---------- ---------------- ---------- --------- ---------- -------
2 JONES 7566 MANAGER 2975 7839
3 SCOTT 7788 ANALYST 3000 7566
3 FORD 7902 ANALYST 3000 7566
2 BLAKE 7698 MANAGER 2850 7839
3 ALLEN 7499 SALESMAN 1600 7698
3 WARD 7521 SALESMAN 1250 7698
3 MARTIN 7654 SALESMAN 1250 7698
3 TURNER 7844 SALESMAN 1500 7698
3 JAMES 7900 CLERK 950 7698
2 CLARK 7782 MANAGER 2450 7839
3 MILLER 7934 CLERK 1300 7782
11 rows selected.
You can answer this query by using subquery as well. Watch…
WITH l2_emp AS (
SELECT Empno FROM Emp
WHERE Mgr IN( SELECT Empno FROM Emp WHERE Mgr IS NULL )
),
l3_emp AS (
SELECT Empno FROM Emp
WHERE Mgr IN( SELECT Empno FROM l2_emp )
)
SELECT
Ename,
Empno,
Job,
Sal,
Mgr
FROM Emp
WHERE Empno IN ( SELECT Empno FROM l2_emp
UNION ALL
SELECT Empno FROM l3_emp );
ENAME EMPNO JOB SAL MGR
----------------- ---------- --------- ---------- ---------
ALLEN 7499 SALESMAN 1600 7698
WARD 7521 SALESMAN 1250 7698
JONES 7566 MANAGER 2975 7839
MARTIN 7654 SALESMAN 1250 7698
BLAKE 7698 MANAGER 2850 7839
CLARK 7782 MANAGER 2450 7839
SCOTT 7788 ANALYST 3000 7566
TURNER 7844 SALESMAN 1500 7698
JAMES 7900 CLERK 950 7698
FORD 7902 ANALYST 3000 7566
MILLER 7934 CLERK 1300 7782
11 rows selected.
You need to understand an attempt to answer a hierarchical query using joins or subqueries is achievable, but at what cost. You have to write multiple conditions, multiple joins and a large query for getting results similar to what hierarchical query can produce.
CONNECT_BY_ISLEAF
CONNECT_BY_ISLEAF is another pseudo column which produces value 1 if the row is a leaf; otherwise, the value is 0. This pseudo column is useful to identify whether a row is a leaf node.
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf
FROM Emp
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF
------------------------- ---------- --------- ---------- ----------
KING 7839 PRESIDENT 5000 0
JONES 7566 MANAGER 2975 0
SCOTT 7788 ANALYST 3000 0
ADAMS 7876 CLERK 1100 1
FORD 7902 ANALYST 3000 0
SMITH 7369 CLERK 800 1
BLAKE 7698 MANAGER 2850 0
ALLEN 7499 SALESMAN 1600 1
WARD 7521 SALESMAN 1250 1
MARTIN 7654 SALESMAN 1250 1
TURNER 7844 SALESMAN 1500 1
JAMES 7900 CLERK 950 1
CLARK 7782 MANAGER 2450 0
MILLER 7934 CLERK 1300 1
14 rows selected.
Q : Get the employees who do not control other employees?
Ans:
SELECT
Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf
FROM Emp
WHERE CONNECT_BY_ISLEAF = 1
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF
--------------------- ---------- --------- ---------- --------
ADAMS 7876 CLERK 1100 1
SMITH 7369 CLERK 800 1
ALLEN 7499 SALESMAN 1600 1
WARD 7521 SALESMAN 1250 1
MARTIN 7654 SALESMAN 1250 1
TURNER 7844 SALESMAN 1500 1
JAMES 7900 CLERK 950 1
MILLER 7934 CLERK 1300 1
8 rows selected.
CONNECT_BY_ISCYCLE
To simplify matters, the CONNECT BY NOCYCLE clause tells the database not to traverse cyclical hierarchies. In this case the CONNECT_BY_ISCYCLE pseudo column generates 1 for record which is responsible for the cycle. For others CONNECT_BY_ISCYCLE value is 0.
CONNECT_BY_ISCYCLE works only with CONNECT BY NOCYCLE clause, else produces error.
--Error! CONNECT_BY_ISCYCLE used without NOCYCLE
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf,
CONNECT_BY_ISCYCLE AS IsCycle
FROM Emp
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
ERROR at line 10:
ORA-30930: NOCYCLE keyword is required with CONNECT_BY_ISCYCLE pseudocolumn
--CONNECT_BY_ISCYCLE used with NOCYCLE with root as KING
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf,
CONNECT_BY_ISCYCLE AS IsCycle
FROM Emp
START WITH Ename = 'KING'
CONNECT BY NOCYCLE PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF ISCYCLE
----------------- ---------- --------- ---------- ---------- -------
KING 7839 PRESIDENT 5000 0 0
JONES 7566 MANAGER 2975 0 0
SCOTT 7788 ANALYST 3000 0 0
ADAMS 7876 CLERK 1100 1 0
FORD 7902 ANALYST 3000 0 0
SMITH 7369 CLERK 800 1 0
BLAKE 7698 MANAGER 2850 0 0
ALLEN 7499 SALESMAN 1600 1 0
WARD 7521 SALESMAN 1250 1 0
MARTIN 7654 SALESMAN 1250 1 0
TURNER 7844 SALESMAN 1500 1 0
JAMES 7900 CLERK 950 1 0
CLARK 7782 MANAGER 2450 0 0
MILLER 7934 CLERK 1300 1 0
14 rows selected.
Now let’s make our EMP table hierarchical data to cyclic hierarchy and see how CONNECT_BY_ISCYCLE is helpful in identifying the nodes which can cause cyclic hierarchy.
UPDATE Emp
SET Mgr = 7369
WHERE Ename = 'KING';
1 row updated.
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal
FROM Emp
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal
FROM Emp
START WITH Ename = 'KING'
CONNECT BY NOCYCLE PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL
-------------------- ---------- -------------------- ----------
KING 7839 PRESIDENT 5000
JONES 7566 MANAGER 2975
SCOTT 7788 ANALYST 3000
ADAMS 7876 CLERK 1100
FORD 7902 ANALYST 3000
SMITH 7369 CLERK 800
BLAKE 7698 MANAGER 2850
ALLEN 7499 SALESMAN 1600
WARD 7521 SALESMAN 1250
MARTIN 7654 SALESMAN 1250
TURNER 7844 SALESMAN 1500
JAMES 7900 CLERK 950
CLARK 7782 MANAGER 2450
MILLER 7934 CLERK 1300
14 rows selected.
--Execute CONNECT_BY_ISCYCLE used with NOCYCLE with root as KING
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf,
CONNECT_BY_ISCYCLE AS IsCycle
FROM Emp
START WITH Ename = 'KING'
CONNECT BY NOCYCLE PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF ISCYCLE
----------------- ---------- --------- ---------- ---------- -------
KING 7839 PRESIDENT 5000 0 0
JONES 7566 MANAGER 2975 0 0
SCOTT 7788 ANALYST 3000 0 0
ADAMS 7876 CLERK 1100 1 0
FORD 7902 ANALYST 3000 0 0
SMITH 7369 CLERK 800 1 1
BLAKE 7698 MANAGER 2850 0 0
ALLEN 7499 SALESMAN 1600 1 0
WARD 7521 SALESMAN 1250 1 0
MARTIN 7654 SALESMAN 1250 1 0
TURNER 7844 SALESMAN 1500 1 0
JAMES 7900 CLERK 950 1 0
CLARK 7782 MANAGER 2450 0 0
MILLER 7934 CLERK 1300 1 0
14 rows selected.
ROLLBACK;
Cycle is identified from root to the leaf nodes. If root or leaf that causes the cycle is eliminated before START WITH and CONNECT BY operation, then CONNECT_BY_ISCYCLE can not identify the cycle as your data does not have any cycle.
UPDATE Emp
SET Mgr = 7369
WHERE Ename = 'KING';
1 row updated.
--Execute CONNECT_BY_ISCYCLE used with NOCYCLE with root as JONES
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf,
CONNECT_BY_ISCYCLE AS IsCycle
FROM Emp
START WITH Ename = 'JONES'
CONNECT BY NOCYCLE PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF ISCYCLE
----------------- ---------- --------- ---------- ---------- -------
JONES 7566 MANAGER 2975 0 0
SCOTT 7788 ANALYST 3000 0 0
ADAMS 7876 CLERK 1100 1 0
FORD 7902 ANALYST 3000 0 0
SMITH 7369 CLERK 800 1 0
14 rows selected.
--Execute CONNECT_BY_ISCYCLE used with NOCYCLE when cycle is filtered
SELECT
LPAD(Ename, LENGTH(Ename)+ LEVEL*2-1) Ename,
Empno,
Job,
Sal,
CONNECT_BY_ISLEAF AS Leaf,
CONNECT_BY_ISCYCLE AS IsCycle
FROM Emp
WHERE Ename <> 'KING'
START WITH Ename = 'KING'
CONNECT BY NOCYCLE PRIOR Empno = Mgr;
ENAME EMPNO JOB SAL LEAF ISCYCLE
----------------- ---------- --------- ---------- ---------- -------
JONES 7566 MANAGER 2975 0 0
SCOTT 7788 ANALYST 3000 0 0
ADAMS 7876 CLERK 1100 1 0
FORD 7902 ANALYST 3000 0 0
SMITH 7369 CLERK 800 1 0
BLAKE 7698 MANAGER 2850 0 0
ALLEN 7499 SALESMAN 1600 1 0
WARD 7521 SALESMAN 1250 1 0
MARTIN 7654 SALESMAN 1250 1 0
TURNER 7844 SALESMAN 1500 1 0
JAMES 7900 CLERK 950 1 0
CLARK 7782 MANAGER 2450 0 0
MILLER 7934 CLERK 1300 1 0
13 rows selected.
ROLLBACK;