If a Table contains hierarchical data then you can select rows in a hierarchical order using the hierarchical query clause.
- Top Down Tree Structure
— Query to display the complete organizational structure of the Employee Table starting with the Top Manager of the Company —
SELECT empno,ename,job,mgr,level
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY level
/
— Prior is an Operator which is used with CONNECT BY clause —
— It decides the direction of hierarchy flow based on the condition specified —
— While PRIOR is mentioned with Column it displays the Parent Column Data —
SELECT ename||’ Reports to : ‘|| PRIOR ename “Walk Top Bottom”
FROM emp
START WITH ename = ‘KING’
CONNECT BY PRIOR empno = mgr
/
SELECT ename||’ Reports to : ‘|| PRIOR ename “Walk Top Bottom”
FROM emp
START WITH ename = ‘JONES’
CONNECT BY PRIOR empno = mgr
/
SELECT ename||’ Reports to : ‘|| ename “Walk Top Bottom”
FROM emp
START WITH ename = ‘KING’
CONNECT BY PRIOR empno = mgr
/
— Query to Display the LEVEL of KING and the level of subordinates reporting to him directly —
SELECT empno,
ename,
job,
mgr,
hiredate,
LEVEL
FROM emp
WHERE LEVEL <= 2
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
/
— Query to Display the Format Output in a Graphical Structure —
SELECT Lpad(ename,Length(ename) + LEVEL * 10 – 10,’-‘)
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY LEVEL
/
- Additional Concepts in Hierarchical Query
— Using LEVEL Pseudocolumn —
Explicitly show the rank or level of a row in the hierarchy by using the LEVEL pseudocolumn.
— Create a report displaying the Company Management Levels beginning with the highest level and indenting each of the following levels.
COLUMN ORG_CHART FORMAT A12
SELECT LPAD(ENAME,LENGTH(ENAME) + (LEVEL * 2) – 2,’ ‘) AS ORG_CHART,LEVEL
FROM EMP
START WITH ENAME = ‘KING’
CONNECT BY PRIOR EMPNO = MGR
/
— ORDER SIBLINGS BY Clause preserves the hierarchy and sorts the children of each parent —
SELECT LPAD(ENAME,LENGTH(ENAME) + (LEVEL * 2) – 2,’ ‘) AS ORG_CHART,LEVEL
FROM EMP
START WITH ENAME = ‘KING’
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME
/
SQL performs the LPAD for the ENAME Col with ‘‘ character until the length of the resultant string ie.ENAME is equal to the value determined by LENGTH(ENAME) + (LEVEL * 2) – 2 Ex For KING Level is 1 so (1 * 2) – 2 = 2 – 2 = 0 So KING Employee does not get padded with any ‘‘ character.
JONES Level is 2 so (2 * 2) – 2 = 4 – 2 = 2 So Jones Employee gets LPAD’ed with 2 spaces.
- Pruning Branches from a Hierarchical Tree
The meaning of the word Pruning is to cut off or remove dead or living parts or branches.
We can use the WHERE and CONNECT BY Clauses to prune the tree i.e to control which nodes or rows are to be displayed.
— Use the WHERE clause to eliminate a single node in turns which displays its child if any. —
Ex –
SELECT DEPTNO,EMPNO,ENAME,JOB,SAL,LEVEL
FROM EMP
WHERE ENAME != ‘JONES’
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
/
— Use the CONNECT clause to eliminate a branch in turns that does not display the child if any. —
SELECT DEPTNO,EMPNO,ENAME,JOB,SAL,LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
and ENAME != ‘JONES’
/