| 2. 40. 1. Hierarchical Queries |
|
|
Using the CONNECT BY and START WITH Clauses |
The syntax for the CONNECT BY and START WITH clauses of a SELECT statement is as follows: |
|
SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
|
|
where |
- LEVEL is a pseudo-column that tells you how far into a tree you are.
- LEVEL returns 1 for a root node, 2 for a child of the root, and so on.
- start_condition specifies where to start the hierarchical query from.
- You must specify a START WITH clause when writing a hierarchical query.
- An example start_condition is employee_id = 1, which specifies the query starts from employee #1.
- prior_condition specifies the relationship between the parent and child rows.
- You must specify a CONNECT BY PRIOR clause when writing a hierarchical query.
|
An example prior_condition is employee_id = manager_id, which specifies the relationship is between the parent employee_id and the child manager_id-that is, the child's manager_id points to the parent's employee_id. |
Quote from: |
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback) |
# Paperback: 608 pages |
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004) |
# Language: English |
# ISBN-10: 0072229810 |
# ISBN-13: 978-0072229813 |