DB2 Version 9.7 for Linux, UNIX, and Windows

SYS_CONNECT_BY_PATH scalar function

The SYS_CONNECT_BY_PATH function (in the SYSIBM schema) is used in hierarchical queries to build a string representing a path from the root row to this row.


>>-SYS_CONNECT_BY_PATH--(--string-expression1--,--string-expression2--)-><

The string for a given row at LEVEL n is built as follows:

Step 1 (using the values of the root row from the first intermediate result table H₁):
```
path₁ := string-expression2 || string-expression1
```

Step n (based on the row from the intermediate result table H_n):

path_n := path_n-1 || string-expression2 || string-expression1

string-expression1: A character string expression that identifies the row. The expression must not include a NEXT VALUE expression for a sequence (SQLSTATE 428F9), any hierarchical query construct, such as the LEVEL pseudocolumn or the CONNECT_BY_ROOT operator (SQLSTATE 428H4), an OLAP function, or an aggregate function (SQLSTATE 428H4).
string-expression2: A constant string that serves as a separator. The expression must not include a NEXT VALUE expression for a sequence (SQLSTATE 428F9), any hierarchical query construct, such as the LEVEL pseudocolumn or the CONNECT_BY_ROOT operator (SQLSTATE 428H4), an OLAP function, or an aggregate function (SQLSTATE 428H4).

The result is a varying-length character string. The length attribute of the result data type is the greater of 1000 and the length attribute of string-expression1.

Rules:

The SYS_CONTEXT_BY_PATH function must not be used outside of the context of a hierarchical query (SQLSTATE 428H4).
The function cannot be used in a START WITH clause or a CONNECT BY clause (SQLSTATE 428H4).

Example:

Return the hierarchy of departments in the DEPARTMENT table.

   SELECT CAST(SYS_CONNECT_BY_PATH(DEPTNAME, '/')
       AS VARCHAR(76)) AS ORG
     FROM DEPARTMENT START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

This query returns:

ORG
-----------------------------------------------------------------------
/SPIFFY COMPUTER SERVICE DIV.
/SPIFFY COMPUTER SERVICE DIV./PLANNING
/SPIFFY COMPUTER SERVICE DIV./INFORMATION CENTER
/SPIFFY COMPUTER SERVICE DIV./DEVELOPMENT CENTER
/SPIFFY COMPUTER SERVICE DIV./DEVELOPMENT CENTER/MANUFACTURING SYSTEMS
/SPIFFY COMPUTER SERVICE DIV./DEVELOPMENT CENTER/ADMINISTRATION SYSTEMS
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/OPERATIONS
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/SOFTWARE SUPPORT
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/BRANCH OFFICE F2
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/BRANCH OFFICE G2
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/BRANCH OFFICE H2
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/BRANCH OFFICE I2
/SPIFFY COMPUTER SERVICE DIV./SUPPORT SERVICES/BRANCH OFFICE J2