Writing Oracle Heirarchical Queries

Oracle provides an easy way to deal with a hierarchy relationship within a database, through a type of query called a hierarchical query. The query allows you to use the hierarchy relationship as the criteria for showing records. Here is a simple version of this query using the EMPLOYEES table from the HR sample schema.

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
EMP.EMPLOYEE_ID,
EMP.MANAGER_ID
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID

The results from this query are as follows:
LAST_NAME FIRST_NAME EMPLOYEE_ID MANAGER_ID
Kochhar Neena 101 100
Whalen Jennifer 200 101
Mavris Susan 203 101
Baer Hermann 204 101
Higgins Shelley 205 101
Gietz William 206 205
Greenberg Nancy 108 101
Faviet Daniel 109 108
Chen John 110 108
Sciarra Ismael 111 108
Urman Jose Manuel 112 108
Popp Luis 113 108

In the above query the SELECT and the FROM are the same as any other query. The Hierarchical part begins at the START WITH clause. This clause indicates which record(s) the query will start with.

The next part is the CONNECT BY…PRIOR clause, the CONNECT BY clause indicates which columns will be compared to determine the hierarchical relationship. The PRIOR clause is used to determine what column will be used to get the next value.

In the above example
The START WITH clause tells the query that it should start with any row that has an employee_id of 101.

The CONNECT_BY clause tells the query to look for the relationship in columns EMPLOYEE_ID and MANAGER_ID

The PRIOR clause tells the query where to get the next value to evaluate; in this case it finds all rows where the MANAGER_ID is 101. It then takes the values from the EMPLOYEE_ID column for all those rows and uses those values to find all rows in the MANAGER_ID column that match the new set of values. This process will continue until it gets to values in the EMPLOYEE_ID column that it cannot find in the MANAGER_ID column.

If the CONNECT BY clause read EMP.EMPLOYEE_ID = PRIOR EMP.MANAGER_ID so that the PRIOR is in front of the MANAGER_ID instead of the EMPLOYEE_ID then it would again start with any row that has 101 In the EMPLOYEE_ID column, however instead of going to the EMPLOYEE_ID column to get the next value to evaluate it would use the value from the MANAGER_ID column and find where those values from the MANAGER_ID column existed in EMPLOYEE_ID column. If we change the above query to read CONNECT BY EMP.EMPLOYEE_ID = PRIOR EMP.MANAGER_ID it would be going up the chain of command instead of down it.

There are additional clauses and pseudocolumns which can be added to the hierarchical query which in some cases are necessary to prevent the query from raising an error and in other cases add some additional detail to the output.

Using the NOCYCLE clause

I’ll start with the NOCYCLE clause; this clause prevents the query from raising an error if the hierarchy in the table is incorrect and ends up looping back on itself which would create an infinite loop

Example

If the MANAGER_ID field for Kochhar was accidentally set to 108 the following error would occur:

“ORA-01436: CONNECT BY loop in user data”

This is because 108 is Greenberg who reports to Kochhar, so the query sees the data like this

Kochhar
GreenBerg
Kochhar
Greeberg
. . .

And the loop would just keep going.

To allow the query to run instead of showing an error add the NOCYCLE clause right after the CONNECT BY clause

Example

Select EMP.LAST_NAME, EMP.FIRST_NAME, EMP.EMPLOYEE_ID, EMP.MANAGER_ID
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID

CONNECT_BY_ISCYCLE Pseudocolumn

Once the NOCYCLE clause has been added to the query to allow it to run a pseudocolumn called CONNECT_BY_ISCYCLE can be used to find the problem. Adding this column to your query will show where the loop is, it does this by displaying a 1 for records that are in the loop and 0 for records that are not.

Here is the query with the CONNECT_BY_ISCYCLE clause added.

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
EMP.EMPLOYEE_ID,
EMP.MANAGER_ID,
CONNECT_BY_ISCYCLE
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID

And here are the results from this query
LAST_NAME FIRST_NAME EMPLOYEE_ID MANAGER_ID CONNECT_BY_ISCYCLE
Kochhar Neena 101 108 0
Whalen Jennifer 200 101 0
Mavris Susan 203 101 0
Baer Hermann 204 101 0
Higgins Shelley 205 101 0
Gietz William 206 205 0
Greenberg Nancy 108 101 1
Faviet Daniel 109 108 0
Chen John 110 108 0
Sciarra Ismael 111 108 0
Urman Jose Manuel 112 108 0
Popp Luis 113 108 0

In the above example the query is displaying 1 in the record associated with Greenberg, so this shows us where the loop is taking place and a correction can be made.

SYS_CONNECT_BY_PATH Pseudocolumn

The next pseudocolumn is called SYS_CONNECT_BY_PATH; this column is very helpful in illustrating the relationship between records. This pseudocolumn displays a list in each row showing the hierarchical relationship to the record(s) indicated in the START WITH clause. Additionally you can tell the statement what character you want to separate each value with.

Here is an example query:

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
EMP.EMPLOYEE_ID,
EMP.MANAGER_ID,
SYS_CONNECT_BY_PATH(Last_name, '/') Last_name_path,
SYS_CONNECT_BY_PATH(EMPLOYEE_ID, '-') ID_PATH
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID

And here are the results:
LAST_NAME FIRST_NAME EMPLOYEE_ID MANAGER_ID LAST_NAME_PATH ID_PATH
Faviet Daniel 109 108 /Kochhar/Greenberg/Faviet , 101, 108, 109
Baer Hermann 204 101 /Kochhar/Baer , 101, 204
Sciarra Ismael 111 108 /Kochhar/Greenberg/Sciarra , 101, 108, 111
Whalen Jennifer 200 101 /Kochhar/Whalen , 101, 200
Chen John 110 108 /Kochhar/Greenberg/Chen , 101, 108, 110
Urman Jose Manuel 112 108 /Kochhar/Greenberg/Urman , 101, 108, 112
Popp Luis 113 108 /Kochhar/Greenberg/Popp , 101, 108, 113
Greenberg Nancy 108 101 /Kochhar/Greenberg , 101, 108
Kochhar Neena 101 100 /Kochhar , 101
Higgins Shelley 205 101 /Kochhar/Higgins , 101, 205
Mavris Susan 203 101 /Kochhar/Mavris , 101, 203
Gietz William 206 205 /Kochhar/Higgins/Gietz , 101, 205, 206

LEVEL Pseudocolumn

Another pseudocolumn that can be used in a hierarchical query is called LEVEL. This column displays the number of levels a record is away from the starting record in the hierarchy. So if the START WITH clause has EMPLOYEE_ID = 101 which is Kochhar, Kochhar will be level 1, Greenberg will be level 2 (as would anyone else who reported directly to Kochhar) and Chen would be level 3 along with anyone else who reported to a manager at level 2. Here is an example of a query using the LEVEL pseudocolumn.

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
LEVEL,
SYS_CONNECT_BY_PATH(Last_name, '/') Last_name_path,
SYS_CONNECT_BY_PATH(EMPLOYEE_ID, ', ') ID_PATH
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID

Here are the results:
LAST_NAME FIRST_NAME LEVEL LAST_NAME_PATH ID_PATH
Kochhar Neena 1 /Kochhar , 101
Whalen Jennifer 2 /Kochhar/Whalen , 101, 200
Mavris Susan 2 /Kochhar/Mavris , 101, 203
Baer Hermann 2 /Kochhar/Baer , 101, 204
Higgins Shelley 2 /Kochhar/Higgins , 101, 205
Gietz William 3 /Kochhar/Higgins/Gietz , 101, 205, 206
Greenberg Nancy 2 /Kochhar/Greenberg , 101, 108
Faviet Daniel 3 /Kochhar/Greenberg/Faviet , 101, 108, 109
Chen John 3 /Kochhar/Greenberg/Chen , 101, 108, 110
Sciarra Ismael 3 /Kochhar/Greenberg/Sciarra , 101, 108, 111
Urman Jose Manuel 3 /Kochhar/Greenberg/Urman , 101, 108, 112
Popp Luis 3 /Kochhar/Greenberg/Popp , 101, 108, 113

Ordering Results using ORDER BY SIBLING

If you wanted to order the query result within the Hierarchical structure the ORDER BY SIBLING clause can be used. For example if the you wanted to order by last name however you also wanted to keep related record next to each other this clause would be used. To show the difference here are two queries and the results. One with just the ORDER BY clause and one with the ORDER SIBLINGS BY clause

ORDER BY clause query and results:

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
LEVEL,
SYS_CONNECT_BY_PATH(Last_name, '/') Last_name_path,
SYS_CONNECT_BY_PATH(EMPLOYEE_ID, ', ') ID_PATH
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID
ORDER BY Last_name

LAST_NAME FIRST_NAME LEVEL LAST_NAME_PATH ID_PATH
Baer Hermann 2 /Kochhar/Baer , 101, 204
Chen John 3 /Kochhar/Greenberg/Chen , 101, 108, 110
Faviet Daniel 3 /Kochhar/Greenberg/Faviet , 101, 108, 109
Gietz William 3 /Kochhar/Higgins/Gietz , 101, 205, 206
Greenberg Nancy 2 /Kochhar/Greenberg , 101, 108
Higgins Shelley 2 /Kochhar/Higgins , 101, 205
Kochhar Neena 1 /Kochhar , 101
Mavris Susan 2 /Kochhar/Mavris , 101, 203
Popp Luis 3 /Kochhar/Greenberg/Popp , 101, 108, 113
Sciarra Ismael 3 /Kochhar/Greenberg/Sciarra , 101, 108, 111
Urman Jose Manuel 3 /Kochhar/Greenberg/Urman , 101, 108, 112
Whalen Jennifer 2 /Kochhar/Whalen , 101, 200

ORDER SIBLINGS BY query and results

Select EMP.LAST_NAME,
EMP.FIRST_NAME,
LEVEL,
SYS_CONNECT_BY_PATH(Last_name, '/') Last_name_path,
SYS_CONNECT_BY_PATH(EMPLOYEE_ID, ', ') ID_PATH
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID
ORDER SIBLINGS BY Last_name
LAST_NAME FIRST_NAME LEVEL LAST_NAME_PATH ID_PATH
Kochhar Neena 1 /Kochhar , 101
Baer Hermann 2 /Kochhar/Baer , 101, 204
Greenberg Nancy 2 /Kochhar/Greenberg , 101, 108
Chen John 3 /Kochhar/Greenberg/Chen , 101, 108, 110
Faviet Daniel 3 /Kochhar/Greenberg/Faviet , 101, 108, 109
Popp Luis 3 /Kochhar/Greenberg/Popp , 101, 108, 113
Sciarra Ismael 3 /Kochhar/Greenberg/Sciarra , 101, 108, 111
Urman Jose Manuel 3 /Kochhar/Greenberg/Urman , 101, 108, 112
Higgins Shelley 2 /Kochhar/Higgins , 101, 205
Gietz William 3 /Kochhar/Higgins/Gietz , 101, 205, 206
Mavris Susan 2 /Kochhar/Mavris , 101, 203
Whalen Jennifer 2 /Kochhar/Whalen , 101, 200

As can be seen in the above results the Ordering will be more meaningful using the ORDER SIBLINGS BY clause instead of just the ORDER BY clause.

USING THE LPAD FUNCTION

A useful function in displaying the Hierarchical relationship in the query results is the lpad character function which can be used in conjunction with the LEVEL pseudocolumn to help display hierarchical relationship visually.

Here is an example of a query using the lpad function

Select Lpad(' ',2*(level-1)) ||Last_name ||', '||First_name "LIST",
LEVEL,
SYS_CONNECT_BY_PATH(Last_name, '/') Last_name_path,
SYS_CONNECT_BY_PATH(EMPLOYEE_ID, ', ') ID_PATH
From HR.EMPLOYEES EMP
Start with EMP.EMPLOYEE_ID = 101
CONNECT BY NOCYCLE PRIOR EMP.EMPLOYEE_ID = EMP.MANAGER_ID
ORDER SIBLINGS BY Last_name.

Here is how the results are displayed:
LIST LEVEL LAST_NAME_PATH ID_PATH
Kochhar, Neena 1 /Kochhar , 101
Baer, Hermann 2 /Kochhar/Baer , 101, 204
Greenberg, Nancy 2 /Kochhar/Greenberg , 101, 108
Chen, John 3 /Kochhar/Greenberg/Chen , 101, 108, 110
Faviet, Daniel 3 /Kochhar/Greenberg/Faviet , 101, 108, 109
Popp, Luis 3 /Kochhar/Greenberg/Popp , 101, 108, 113
Sciarra, Ismael 3 /Kochhar/Greenberg/Sciarra , 101, 108, 111
Urman, Jose Manuel 3 /Kochhar/Greenberg/Urman , 101, 108, 112
Higgins, Shelley 2 /Kochhar/Higgins , 101, 205
Gietz, William 3 /Kochhar/Higgins/Gietz , 101, 205, 206
Mavris, Susan 2 /Kochhar/Mavris , 101, 203
Whalen, Jennifer 2 /Kochhar/Whalen , 101, 200