Hierarchical Queries using SYS_CONNECT_BY_PATH clause

Hierarchical Queries using SYS_CONNECT_BY_PATH clause


Purpose

SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row

returned by CONNECT BY condition.

Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as

column.

Examples

The following example returns the path of employee names from employee KING to all employees of KING (and their employees):


SELECT LPAD (' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH (ename, '/') "Path"
      FROM scott.emp
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

Path
---------------------------------------------------------------
  /KING
   /KING/JONES
     /KING/JONES/SCOTT
       /KING/JONES/SCOTT/ADAMS
     /KING/JONES/FORD
       /KING/JONES/FORD/SMITH
   /KING/BLAKE
     /KING/BLAKE/ALLEN
     /KING/BLAKE/WARD
     /KING/BLAKE/MARTIN
     /KING/BLAKE/TURNER
     /KING/BLAKE/JAMES
   /KING/CLARK
     /KING/CLARK/MILLER