Oracle provides following functions to deal with hierarchical queries.
Functions
- CONNECT_BY_ROOT : Returns the root node(s) associated with the current row.
- SYS_CONNECT_BY_PATH : Returns a delimited breadcrumb from root to the current row
CONNECT_BY_ROOT
CONNECT_BY_ROOT is a unary operator that is valid only in Hierarchical queries. We should qualify a column with this operator, then Oracle returns the column value using data from the root row. Qualifying column in CONNECT_BY_ROOT in parenthesis is not mandatory, but it is good to do so. CONNECT_BY_ROOT extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical query.
SELECT Ename, Job, CONNECT_BY_ROOT(Ename) Boss FROM Emp START WITH Job = 'PRESIDENT' CONNECT BY PRIOR Empno = Mgr; ENAME JOB BOSS ---------- --------- ---------- KING PRESIDENT KING JONES MANAGER KING SCOTT ANALYST KING ADAMS CLERK KING FORD ANALYST KING SMITH CLERK KING CLARK MANAGER KING MILLER CLERK KING BLAKE MANAGER KING ALLEN SALESMAN KING WARD SALESMAN KING MARTIN SALESMAN KING TURNER SALESMAN KING JAMES CLERK KING 14 rows selected.
SELECT Ename, Job, CONNECT_BY_ROOT(Ename) Boss FROM Emp START WITH Ename = 'JONES' CONNECT BY PRIOR Empno = Mgr; ENAME JOB BOSS ---------- --------- ---------- JONES MANAGER JONES SCOTT ANALYST JONES ADAMS CLERK JONES FORD ANALYST JONES SMITH CLERK JONES
SELECT Ename, Job, CONNECT_BY_ROOT(Ename) Boss FROM Emp START WITH Job = 'MANAGER' CONNECT BY PRIOR Empno = Mgr; ENAME JOB BOSS ---------- --------- ---------- JONES MANAGER JONES SCOTT ANALYST JONES ADAMS CLERK JONES FORD ANALYST JONES SMITH CLERK JONES CLARK MANAGER CLARK MILLER CLERK CLARK BLAKE MANAGER BLAKE ALLEN SALESMAN BLAKE WARD SALESMAN BLAKE MARTIN SALESMAN BLAKE TURNER SALESMAN BLAKE JAMES CLERK BLAKE 13 rows selected.
Q : Get the employee name, job, salary with the organization head name, salary and how much less the employees are paid compared to the organization head?
Ans:
SELECT
Ename,
Job,
Sal,
CONNECT_BY_ROOT Ename BOSS,
CONNECT_BY_ROOT Sal "BOSS SALARY",
CONNECT_BY_ROOT Sal - Sal "SALARY DIFF"
FROM Emp
START WITH Empno = 7839
CONNECT BY PRIOR Empno = Mgr;
ENAME JOB SAL BOSS BOSS SALARY SALARY DIFF
---------- --------- ---------- ---------- ----------- -----------
KING PRESIDENT 5000 KING 5000 0
JONES MANAGER 2975 KING 5000 2025
SCOTT ANALYST 3000 KING 5000 2000
ADAMS CLERK 1100 KING 5000 3900
FORD ANALYST 3000 KING 5000 2000
SMITH CLERK 800 KING 5000 4200
CLARK MANAGER 2450 KING 5000 2550
MILLER CLERK 1300 KING 5000 3700
BLAKE MANAGER 2850 KING 5000 2150
ALLEN SALESMAN 1600 KING 5000 3400
WARD SALESMAN 1250 KING 5000 3750
MARTIN SALESMAN 1250 KING 5000 3750
TURNER SALESMAN 1500 KING 5000 3500
JAMES CLERK 950 KING 5000 4050
14 rows selected.
Q : Get the employee name, job, joining date with the organization head name, joining date and how much experience difference the employees have with respect to the organization head?
Ans:
SELECT
Ename,
Job,
HireDate JoinDate,
CONNECT_BY_ROOT(Ename) Boss,
CONNECT_BY_ROOT(HireDate) "BOSS JOINDATE",
RTRIM(ROUND(MONTHS_BETWEEN(CONNECT_BY_ROOT(HireDate), HireDate)/ 12, 1)||' Yrs', 8) "EXP DIFF"
FROM Emp
START WITH Sal = 5000
CONNECT BY PRIOR Empno = Mgr;
ENAME JOB JOINDATE BOSS BOSS JOINDATE EXP DIFF
---------- --------- --------- ---------- ------------- ------------
KING PRESIDENT 17-NOV-81 KING 17-NOV-81 0 Yrs
JONES MANAGER 02-APR-81 KING 17-NOV-81 .6 Yrs
SCOTT ANALYST 19-APR-87 KING 17-NOV-81 -5.4 Yrs
ADAMS CLERK 23-MAY-87 KING 17-NOV-81 -5.5 Yrs
FORD ANALYST 03-DEC-81 KING 17-NOV-81 0 Yrs
SMITH CLERK 17-DEC-80 KING 17-NOV-81 .9 Yrs
CLARK MANAGER 09-JUN-81 KING 17-NOV-81 .4 Yrs
MILLER CLERK 23-JAN-82 KING 17-NOV-81 -.2 Yrs
BLAKE MANAGER 01-MAY-81 KING 17-NOV-81 .5 Yrs
ALLEN SALESMAN 20-FEB-81 KING 17-NOV-81 .7 Yrs
WARD SALESMAN 22-FEB-81 KING 17-NOV-81 .7 Yrs
MARTIN SALESMAN 28-SEP-81 KING 17-NOV-81 .1 Yrs
TURNER SALESMAN 08-SEP-81 KING 17-NOV-81 .2 Yrs
JAMES CLERK 03-DEC-81 KING 17-NOV-81 0 Yrs
14 rows selected.
Q : Get the details of the employees who have joined before the organization head?
Ans:
SELECT
Ename,
Job,
HireDate JoinDate,
CONNECT_BY_ROOT(Ename) Boss,
CONNECT_BY_ROOT(HireDate) "BOSS JOINDATE",
RTRIM(ROUND(MONTHS_BETWEEN(CONNECT_BY_ROOT(HireDate), HireDate), 1)||' Months', 12) "EXP DIFF"
FROM Emp
WHERE SIGN(MONTHS_BETWEEN(CONNECT_BY_ROOT(HireDate), HireDate)) = 1
START WITH Sal = 5000
CONNECT BY PRIOR Empno = Mgr;
ENAME JOB JOINDATE BOSS BOSS JOINDATE EXP DIFF
---------- --------- --------- ---------- ------------- ------------
JONES MANAGER 02-APR-81 KING 17-NOV-81 7.5 Months
SMITH CLERK 17-DEC-80 KING 17-NOV-81 11 Months
CLARK MANAGER 09-JUN-81 KING 17-NOV-81 5.3 Months
BLAKE MANAGER 01-MAY-81 KING 17-NOV-81 6.5 Months
ALLEN SALESMAN 20-FEB-81 KING 17-NOV-81 8.9 Months
WARD SALESMAN 22-FEB-81 KING 17-NOV-81 8.8 Months
MARTIN SALESMAN 28-SEP-81 KING 17-NOV-81 1.6 Months
TURNER SALESMAN 08-SEP-81 KING 17-NOV-81 2.3 Months
8 rows selected.
SYS_CONNECT_BY_PATH
Display the full path from the current row to its root.
--Get path from top to bottom for PRESIDENT SELECT Ename, SYS_CONNECT_BY_PATH(Ename, '/') Path FROM Emp START WITH Job = 'PRESIDENT' CONNECT BY PRIOR Empno = Mgr; NAME PATH ---------- ------------------------------ KING /KING JONES /KING/JONES SCOTT /KING/JONES/SCOTT ADAMS /KING/JONES/SCOTT/ADAMS FORD /KING/JONES/FORD SMITH /KING/JONES/FORD/SMITH CLARK /KING/CLARK MILLER /KING/CLARK/MILLER BLAKE /KING/BLAKE ALLEN /KING/BLAKE/ALLEN WARD /KING/BLAKE/WARD MARTIN /KING/BLAKE/MARTIN TURNER /KING/BLAKE/TURNER JAMES /KING/BLAKE/JAMES 14 rows selected.
--Get path from bottom to top for SMITH SELECT Ename, SYS_CONNECT_BY_PATH(Ename, '/') Path FROM Emp START WITH Ename = 'SMITH' CONNECT BY Empno = PRIOR Mgr; ENAME PATH ---------- ------------------------------ SMITH /SMITH FORD /SMITH/FORD JONES /SMITH/FORD/JONES KING /SMITH/FORD/JONES/KING
Q : Get the hierarchy of every job in the organization?
Ans:
SELECT DISTINCT Job, SYS_CONNECT_BY_PATH(Job, '/') Path FROM Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = Mgr; JOB PATH --------- -------------------------------------------------- PRESIDENT /PRESIDENT CLERK /PRESIDENT/MANAGER/ANALYST/CLERK SALESMAN /PRESIDENT/MANAGER/SALESMAN CLERK /PRESIDENT/MANAGER/CLERK ANALYST /PRESIDENT/MANAGER/ANALYST MANAGER /PRESIDENT/MANAGER 6 rows selected.