Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL START WITH Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 217 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?