Retrieving a Path with SYS_CONNECT_BY_PATH v11
SYS_CONNECT_BY_PATH
is a function that works within a hierarchical query to retrieve the column values of a specified column that occur between the current node and the root node. The signature of the function is:
SYS_CONNECT_BY_PATH (<column>, <delimiter>)
The function takes two arguments:
column
is the name of a column that resides within a table specified in the hierarchical query that is calling the function.
delimiter
is the varchar
value that separates each entry in the specified column.
The following example returns a list of employee names, and their managers; if the manager has a manager, that name is appended to the result:
edb=# SELECT level, ename , SYS_CONNECT_BY_PATH(ename, '/') managers FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL ORDER BY level, ename, managers; level | ename | managers -------+--------+------------------------- 1 | KING | /KING 2 | BLAKE | /KING/BLAKE 2 | CLARK | /KING/CLARK 2 | JONES | /KING/JONES 3 | ALLEN | /KING/BLAKE/ALLEN 3 | FORD | /KING/JONES/FORD 3 | JAMES | /KING/BLAKE/JAMES 3 | MARTIN | /KING/BLAKE/MARTIN 3 | MILLER | /KING/CLARK/MILLER 3 | SCOTT | /KING/JONES/SCOTT 3 | TURNER | /KING/BLAKE/TURNER 3 | WARD | /KING/BLAKE/WARD 4 | ADAMS | /KING/JONES/SCOTT/ADAMS 4 | SMITH | /KING/JONES/FORD/SMITH (14 rows)
Within the result set:
- The
level
column displays the number of levels that the query returned. - The
ename
column displays the employee name. - The
managers
column contains the hierarchical list of managers.
The Advanced Server implementation of SYS_CONNECT_BY_PATH
does not support use of:
SYS_CONNECT_BY_PATH
insideCONNECT_BY_PATH
SYS_CONNECT_BY_PATH
insideSYS_CONNECT_BY_PATH