Skip to Main Content
Mastering Oracle SQL
book

Mastering Oracle SQL

by Sanjay Mishra, Alan Beaulieu
April 2002
Intermediate to advanced content levelIntermediate to advanced
336 pages
9h 58m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL

Oracle SQL

Dan Hotka
Oracle SQL Developer

Oracle SQL Developer

Ajith Narayanan, Susan Harper

Publisher Resources

ISBN: 0596001290Supplemental ContentCatalog PageErrata