SYS_CONNECT_BY_PATH

The SYS_CONNECT_BY_PATH function 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₁):
```
path1 := 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. It must be a built-in character string data type. The expression must not include a NEXT VALUE expression for a sequence, any hierarchical query construct such as the LEVEL pseudocolumn or the CONNECT_BY_ROOT operator, an OLAP specification, or an aggregate function.
string-expression2: A character string expression that serves as a separator. It must be a built-in character string data type. The expression must not include a NEXT VALUE expression for a sequence, any hierarchical query construct such as the LEVEL pseudocolumn or the CONNECT_BY_ROOT operator, an OLAP specification, or an aggregate function.

The result is a CLOB(1M).

The SYS_CONNECT_BY_PATH function must not be used outside of the context of a hierarchical query. It cannot be used in a START WITH clause or a CONNECT BY clause.

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