The PRIOR unary operator is for use only in the CONNECT BY clause of hierarchical queries. To get all subordinates over all levels, the PRIOR operator must be added to the CONNECT BY clause of the hierarchical query.
The CONNECT BY clause performs an inner join between the intermediate result table Hn of a hierarchical query and the source result table that you specify in the FROM clause. All column references to tables that are referenced in the FROM clause and that are arguments to the PRIOR operator are considered to range over table Hn.
The result data type of the operator is the result data type of the expression.
CONNECT BY PRIOR T.PK = T.FK
If
the primary key is a composite key, prefix each column with PRIOR,
as shown in the following example: CONNECT BY PRIOR T.PK1 = T.FK1 AND PRIOR T.PK2 = T.FK2
PRIOR FIRSTNME || LASTNAME
That
expression is equivalent to the first one in the following list but
not the second one: (PRIOR FIRSTNME) || LASTNAME
PRIOR (FIRSTNME || LASTNAME)
If you specify the PRIOR operator outside a CONNECT BY clause of a hierarchical query, SQLSTATE 428H4 is returned.
SELECT LEVEL, DEPTNAME
FROM DEPARTMENT START WITH DEPTNO = 'A00'
CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT
This
query returns the following results:LEVEL DEPTNAME
----------- ----------------------------------------
1 SPIFFY COMPUTER SERVICE DIV.
2 PLANNING
2 INFORMATION CENTER
2 DEVELOPMENT CENTER
3 MANUFACTURING SYSTEMS
3 ADMINISTRATION SYSTEMS
2 SUPPORT SERVICES
3 OPERATIONS
3 SOFTWARE SUPPORT
3 BRANCH OFFICE F2
3 BRANCH OFFICE G2
3 BRANCH OFFICE H2
3 BRANCH OFFICE I2
3 BRANCH OFFICE J2