Quick Search:
 
 The Oracle PL/SQL START WITH Clause      [Return To Index] Jump to:  

Term: START_WITH

Definition:
In Oracle PL/SQL, the START WITH clause appears in hierarchical SQL queries and is used to query data with Master-Detail relationships. It marks a node in the tree which is considered as the starting node or level of the tree structured data. A scalar value or subquery can be used to assign the root of the query.

It is mandatory for use with the CONNECT_BY clause, which indicates relationship between a node and subnode. The parent row can be identified with the PRIOR keyword.

Note that START WITH clause is an optional clause in hierarchical queries.

Example Syntax:

SELECT [COLUMNS]
FROM TABLE_NAME
START WITH [COLUMN] = [ROOT NODE VALUE]
CONNECT BY [CONDITION]


Example Usage:

The SQL query below lists Employees reporting to their Managers at different levels.

SELECT level, LPAD(ename,level*5,' ') ename
FROM employee
start with empno=100
CONNECT_BY by nocycle prior empno = mgr
/


LEVEL ENAME
---------- --------------------
1 JOHN
2 KATE
3 KING
4 HICK
3 SUEZ
2 MILLER
3 LARRY
3 EDWIN
2 JOHNY

9 rows selected.



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org