Quick Search:
 
 The Oracle PL/SQL SYS_CONNECT_BY_PATH Function      [Return To Index] Jump to:  

Term: SYS_CONNECT_BY_PATH

Definition:
In Oracle PL/SQL, SYS_CONNECT_BY_PATH is a function which is used in hierarchical queries to get the path for the current node starting from root node. It accepts two input parameters of [CHAR | VARCHAR2 | NCHAR | NVARCHAR2] type. The first parameter is the column which indicates the scalar value of the root node. The second parameter is the delimiter to be used as the node value separator.

Example Syntax:

SYS_CONNECT_BY_PATH ( column , char )


Example Usage:

The SQL query below displays an employee id, the employee name, and the node path starting from the root node element. Note the root node element in this example is '100'.

SELECT empno,ename,SYS_CONNECT_BY_PATH(ename, '/') root
FROM employee
START_WITH empno=100
CONNECT_BY NOCYCLE prior empno = mgr
/

EMPNO ENAME ROOT
---------- ---------- --------------------
100 JOHN /JOHN
110 KATE /JOHN/KATE
160 KING /JOHN/KATE/KING
180 HICK /JOHN/KATE/KING/HICK
170 SUEZ /JOHN/KATE/SUEZ
120 MILLER /JOHN/MILLER
140 LARRY /JOHN/MILLER/LARRY
150 EDWIN /JOHN/MILLER/EDWIN
130 JOHNY /JOHN/JOHNY



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org