DB2 Version 9.7 for Linux, UNIX, and Windows

PRIOR unary operator

The PRIOR unary operator is for use only in the CONNECT BY clause of hierarchical queries (SQLSTATE 428H4).


>>-PRIOR--expression-------------------------------------------><

The CONNECT BY clause performs an inner join between the intermediate result table H_n of the hierarchical query and the source result table specified in the FROM clause. All column references to tables that are referenced in the FROM clause, and which are arguments to the PRIOR operator, are considered to be ranging over H_n.

The primary key of the intermediate result table H_n is typically joined to the foreign keys of the source result table to recursively traverse the hierarchy.

   CONNECT BY PRIOR T.PK = T.FK

If the primary key is a composite key, care must be taken to prefix each column with PRIOR:

   CONNECT BY PRIOR T.PK1 = T.FK1 AND PRIOR T.PK2 = T.FK2

expression: Any expression that does not contain a NEXT VALUE expression, an hierarchical query construct (such as the LEVEL pseudocolumn), the SYS_CONNECT_BY_PATH function, or an OLAP function (SQLSTATE 428H4).

The result data type of the operator is the result data type of the expression.

Notes:

A PRIOR operator has a higher precedence than any infix operator. Therefore, to pass an expression with infix operators (such as + or ||) as an argument, parentheses must be used. For example:
```
   PRIOR FIRSTNME || LASTNAME
```
returns the FIRSTNME value of the prior row concatenated with the LASTNAME value of the actual row in the hierarchy, because this expression is equivalent to:
```
   (PRIOR FIRSTNME) || LASTNAME
```
rather than:
```
   PRIOR (FIRSTNME || LASTNAME)
```

Example:

Return the hierarchy of departments in the DEPARTMENT table.

   SELECT LEVEL, DEPTNAME
     FROM DEPARTMENT START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

This query returns:

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