Mastering Oracle SQL

Complex Hierarchy Operations

In this section, we discuss how we can use Oracle SQL’s hierarchical extensions to perform complex hierarchical queries.

Finding the Number of Levels

Previously we showed how the LEVEL pseudocolumn generates a level number for each record when we use the START WITH...CONNECT BY clause. We can use the following query to determine the number of levels in the hierarchy by counting the number of distinct level numbers returned by the LEVEL pseudocolumn:

               SELECT COUNT(DISTINCT LEVEL) 
               FROM EMPLOYEE
               START WITH MANAGER_EMP_ID IS NULL
               CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;

COUNT(DISTINCTLEVEL)
--------------------
                   4

To determine the number of employees at each level, group the results by LEVEL and count the number of employees in each distinct group. For example:

               SELECT LEVEL, COUNT(EMP_ID) 
               FROM EMPLOYEE
               START WITH MANAGER_EMP_ID IS NULL
               CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID
               GROUP BY LEVEL;

    LEVEL COUNT(EMP_ID)
--------- -------------
        1             1
        2             3
        3             8
        4             2

Listing Records in Hierarchical Order

One of the very common programming challenges SQL programmers face is to list records in a hierarchy in their proper hierarchical order. For example, we might wish to list employees with their subordinates underneath them, as is in the following query:

               SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || LNAME "EMPLOYEE",
                      EMP_ID, MANAGER_EMP_ID
               FROM EMPLOYEE
               START WITH MANAGER_EMP_ID IS NULL
               CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID; LEVEL Employee EMP_ID MANAGER_EMP_ID --------- ...