DB2 Version 10.1 for Linux, UNIX, and Windows

CONNECT_BY_ROOT unary operator

The CONNECT_BY_ROOT unary operator is for use only in hierarchical queries. For every row in the hierarchy, this operator returns the expression for the root ancestor of the row.


>>-CONNECT_BY_ROOT--expression---------------------------------><

expression: An expression that does not contain a NEXT VALUE expression, a hierarchical query construct (such as the LEVEL pseudocolumn), the SYS_CONNECT_BY_PATH function, or an OLAP function. If you specify any of these items, SQLSTATE 428H4 is returned.

Usage

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

The following rules apply to the CONNECT_BY_ROOT operator:

A CONNECT_BY_ROOT operator has a higher precedence than that of any infix operator, such as the plus sign (+) or double vertical bar (||). Therefore, to pass an expression with infix operators as an argument, you must use parentheses. For example, the following expression returns the FIRSTNME value of the root ancestor row concatenated with the LASTNAME value of the actual row in the hierarchy:
```
   CONNECT_BY_ROOT FIRSTNME || LASTNAME
```
That expression is equivalent to the first one in the following list but not the second one:
```
   (CONNECT_BY_ROOT FIRSTNME) || LASTNAME
   CONNECT_BY_ROOT (FIRSTNME || LASTNAME)
```
A CONNECT_BY_ROOT operator cannot be specified in the START WITH clause or the CONNECT BY clause of a hierarchical query (SQLSTATE 428H4).
A CONNECT_BY_ROOT operator cannot be specified as an argument to the SYS_CONNECT_BY_PATH function (SQLSTATE 428H4).

The following query returns the hierarchy of departments and their root departments in the DEPARTMENT table:

   SELECT CONNECT_BY_ROOT DEPTNAME AS ROOT, DEPTNAME
     FROM DEPARTMENT START WITH DEPTNO IN ('B01','C01','D01','E01')
     CONNECT BY PRIOR DEPTNO = ADMRDEPT

This query returns the following results:

ROOT               DEPTNAME
------------------ -----------------------
PLANNING           PLANNING
INFORMATION CENTER INFORMATION CENTER
DEVELOPMENT CENTER DEVELOPMENT CENTER
DEVELOPMENT CENTER MANUFACTURING SYSTEMS
DEVELOPMENT CENTER ADMINISTRATION SYSTEMS
SUPPORT SERVICES   SUPPORT SERVICES
SUPPORT SERVICES   OPERATIONS
SUPPORT SERVICES   SOFTWARE SUPPORT
SUPPORT SERVICES   BRANCH OFFICE F2
SUPPORT SERVICES   BRANCH OFFICE G2
SUPPORT SERVICES   BRANCH OFFICE H2
SUPPORT SERVICES   BRANCH OFFICE I2
SUPPORT SERVICES   BRANCH OFFICE J2