Blog

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