Mastering Oracle SQL

Restrictions on Hierarchical Queries

The following restrictions apply to hierarchical queries that use START WITH...CONNECT BY:

1. A hierarchical query can’t use a join.

   Tip

   There are ways to overcome this restriction. Chapter 5 discusses one such example under Section 8.3.

2. A hierarchical query cannot select data from a view that involves a join.

3. We can use an ORDER BY clause within a hierarchical query; however, the ORDER BY clause takes precedence over the hierarchical ordering performed by the START WITH...CONNECT BY clause. Therefore, unless all we care about is the level number, it doesn’t make sense to use ORDER BY in a hierarchical query.

The third issue deserves some additional explanation. Let’s look at an example to see what happens when we use ORDER BY in a hierarchical query:

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

    LEVEL Employee        EMP_ID MANAGER_EMP_ID    SALARY
--------- ------------ --------- -------------- ---------
        4       SMITH       7369           7902       800
        3     JAMES         7900           7698       950
        4       ADAMS       7876           7788      1100
        3     WARD          7521           7698      1250
        3     MARTIN        7654           7698      1250
        3     MILLER        7934           7782      1300
        3     TURNER        7844           7698      1500
        3     ALLEN         7499           7698      1600
        2   JONES           7566           7839      2000
        2   CLARK           7782           7839      2450
        2   BLAKE           7698           7839      2850
        3     SCOTT         7788           7566      3000
        3     FORD          7902           7566      3000
        1 KING              7839                     5000

14 rows selected.

The START WITH...CONNECT BY clause arranges the employees in proper hierarchical ...