DB2 Version 10.1 for Linux, UNIX, and Windows

SYS_CONNECT_BY_PATH scalar function

The SYS_CONNECT_BY_PATH function builds a string representing a path from the root to a node in hierarchical queries.


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

The schema is SYSIBM.

string-expression1

A character string expression that identifies the row. The expression must not include any of the items in the following list; otherwise, the SQLSTATE in parentheses is returned:

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 (SQLSTATE 428H4)
An aggregate function (SQLSTATE 428H4)

string-expression2

A constant string that serves as a separator. The expression must not include any of the items in the following list; otherwise, the SQLSTATE in parentheses is returned:

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 (SQLSTATE 428H4)
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.

The string for a particular row at pseudocolumn 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

The following rules apply to the SYS_CONTEXT_BY_PATH function:

If you specify the function outside the context of a hierarchical query, SQLSTATE 428H4 is returned.
If you use the function in a START WITH clause or a CONNECT BY clause, SQLSTATE 428H4 is returned.

The following example returns 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 the following results:

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