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. This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

Read syntax diagramSkip visual syntax diagramSYS_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 units of the result data type is the same as the string units of the data type 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 H1):
    path1 := string-expression2 || string-expression1
  • Step n (based on the row from the intermediate result table Hn):
    pathn := pathn-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