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

Term: PRIOR

Definition:
In Oracle PL/SQL, PRIOR refers to a hierarchical operator which is used with CONNECT_BY clause in hierarchical queries. It decides the direction of hierarchy flow based on the condition specified by CONNECT_BYclause. It can exist on either side of the CONNECT_BY equality conditions. PRIOR is most commonly used when comparing column values with the equality operator.

Note that sequence related expressions cannot be used with the PRIOR operator.

Example Syntax:

SELECT 
FROM
START WITH
CONNECT BY [PRIOR] condition


Example Usage:

The SQL query below lists the all reportees to all managers in an organisation.

SELECT LEVEL, MGR MANAGER, LPAD(EMPNO,5*(LEVEL-1)) REPORTEE
FROM EMPLOYEE
START WITH MGR IS NULL
CONNECT_BY PRIOR EMPNO=MGR
ORDER SIBLINGS BY EMPNO
/

LEVEL MANAGER REPORTEE
---------- ---------- --------------------
1
2 100 110
3 110 160
4 160 180
3 110 170
2 100 120
3 120 140
3 120 150
2 100 130



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 112 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?