Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
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
.
See Also: "Hierarchical Queries" for more information about hierarchical queries andCONNECT BY conditions |
Examples
The following example returns the path of employee names from employee Kochhar
to all employees of Kochhar
(and their employees):
SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees START WITH last_name = 'Kochhar' CONNECT BY PRIOR employee_id = manager_id; Path --------------------------------------------------------------- /Kochhar /Kochhar/Greenberg /Kochhar/Greenberg/Faviet /Kochhar/Greenberg/Chen /Kochhar/Greenberg/Sciarra /Kochhar/Greenberg/Urman /Kochhar/Greenberg/Popp /Kochhar/Whalen /Kochhar/Mavris /Kochhar/Baer /Kochhar/Higgins /Kochhar/Higgins/Gietz