Hierarchical queries in Oracle

 Hierarchical queries

select employee_id,lpad(' ',level*3,' ')||LAST_NAME "Employee Name",

            prior LAST_NAME "Reporting to”, manager_id,  

            connect_by_root LAST_NAME boss,

            level,connect_by_iscycle IFLOOP,

           SYS_CONNECT_BY_PATH(LAST_NAME,'/') Hierarchy,

            connect_by_isleaf ifleaf

  FROM hr.employees

   connect by nocycle prior employee_id = manager_id

      start with manager_id is null

    order siblings by LAST_NAME;

EMPLOYEE_ID	Employee Name	Reporting to	MANAGER_ID	BOSS	LEVEL	IFLOOP	HIARCHY	IFLEAF
130	Atkinson			Atkinson	1	0	/Atkinson	1
129	Bissot			Bissot	1	0	/Bissot	1
185	Bull			Bull	1	0	/Bull	1
187	Cabrio			Cabrio	1	0	/Cabrio	1
102	De Haan			De Haan	1	0	/De Haan	0
103	Hunold	De Haan	102	De Haan	2	0	/De Haan/Hunold	0
105	Austin	Hunold	103	De Haan	3	0	/De Haan/Hunold/Austin	1
104	Ernst	Hunold	103	De Haan	3	0	/De Haan/Hunold/Ernst	1
107	Lorentz	Hunold	103	De Haan	3	0	/De Haan/Hunold/Lorentz	1
106	Pataballa	Hunold	103	De Haan	3	0	/De Haan/Hunold/Pataballa	1
186	Dellinger			Dellinger	1	0	/Dellinger	1
108	Greenberg			Greenberg	1	0	/Greenberg	0
110	Chen	Greenberg	108	Greenberg	2	0	/Greenberg/Chen	1
109	Faviet	Greenberg	108	Greenberg	2	0	/Greenberg/Faviet	1
113	Popp	Greenberg	108	Greenberg	2	0	/Greenberg/Popp	1
111	Sciarra	Greenberg	108	Greenberg	2	0	/Greenberg/Sciarra	1
112	Urman	Greenberg	108	Greenberg	2	0	/Greenberg/Urman	1
100	King			King	1	0	/King	0
148	Cambrault	King	100	King	2	0	/King/Cambrault	0
172	Bates	Cambrault	148	King	3	0	/King/Cambrault/Bates	1
169	Bloom	Cambrault	148	King	3	0	/King/Cambrault/Bloom	1
170	Fox	Cambrault	148	King	3	0	/King/Cambrault/Fox	1
173	Kumar	Cambrault	148	King	3	0	/King/Cambrault/Kumar	1
168	Ozer	Cambrault	148	King	3	0	/King/Cambrault/Ozer	1
171	Smith	Cambrault	148	King	3	0	/King/Cambrault/Smith	1
101	Kochhar	King	100	King	2	0	/King/Kochhar	0
204	Baer	Kochhar	101	King	3	0	/King/Kochhar/Baer	1
205	Higgins	Kochhar	101	King	3	0	/King/Kochhar/Higgins	0
206	Gietz	Higgins	205	King	4	0	/King/Kochhar/Higgins/Gietz	1
203	Mavris	Kochhar	101	King	3	0	/King/Kochhar/Mavris	1
200	Whalen	Kochhar	101	King	3	0	/King/Kochhar/Whalen	1

Let’s analyze it:

How does Oracle make the hierarchy? First of all it reads the records.
Then it determines the roots applying the START WITH clause.
Then, starting from each root, it determines the first-level sons applying the CONNECT BY clause and so on…

1.       the CONNECT BY clause, mandatory to make a hierarchical query, is used to define how each record is connected to the hierarchical superior.

The father of the record having manager_id =x has employee_id =x.
On the other hand, given a record with employee_id =x, all the records having manager_id =x are his sons.
The unary operator PRIOR indicates “the father of”.

2.       START WITH clause is used to from which records we want to start the hierarchy, in our example we want to start from the root of the hierarchy, the employee that has no manager.

3.       The LEVEL pseudocolumn indicates at which level each record stays in the hierarchy, starting from the root that has level=1.

4.       order siblings by: Records order defines the hierarchy

5.       NOCYCLE clause: To avoid loop. That clause tells to Oracle to stop when a loop is raised, Oracle goes on the other branches of the tree.

  

6.      CONNECT_BY_ISCYCLE pseudocolumn tells us in which records a loop has been detected 

  

7       In addition to PRIOR another unary operator exists that’s useful: CONNECT_BY_ROOT. It allows us to display the root of a given record

8      Another useful function is SYS_CONNECT_BY_PATH, it gets as input a field and a character and builds the full path from the root to the current record using the character as a separator

9.     There’s another useful pseudocolumn to show, CONNECT_BY_ISLEAF. It tells us whether a record is a leaf of the tree or not. Value 1 is for Leaf.

Interesting is the ability of CONNECT BY to generate more records from a table that contains only one row.

Ex:

Generate dates for next one year from sysdate

SELECT TRUNC (SYSDATE + ROWNUM) "Next Year"

FROM DUAL

CONNECT BY ROWNUM < 366;

other way of getting next one year of dates

select to_date(sysdate,'dd-mon-yyyy') + rownum -1 "Next Year"

from all_objects

where rownum <= to_date(ADD_MONTHS(SYSDATE,12),'dd-mon-yyyy')-to_date(sysdate,'dd-mon-yyyy');