Restrictions on Hierarchical Queries
The following restrictions apply to hierarchical queries that use START WITH...CONNECT BY:
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.
A hierarchical query cannot select data from a view that involves a join.
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 ...
Get Mastering Oracle SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.