Functions Allowed to Use in Hierarchy Query

Description

The SYS_CONNECT_BY_PATH function returns the hierarchical path from a root to the specified row in string. The column and separator specified as an argument must be a character type. Each path separated by specified separator will be displayed consecutively.

This function can be used in the WHERE and ORDER BY clauses of the SELECT statement.

Syntax

SYS_CONNECT_BY_PATH (column_name, separator_char)

Example

The following example shows how to retrieve path from a root to the specified row.

-- Executing a hierarchical query with SYS_CONNECT_BY_PATH function

SELECT id, mgrid, name, SYS_CONNECT_BY_PATH(name,'/') as [hierarchy]

    FROM tree

    START WITH mgrid IS NULL

    CONNECT BY PRIOR id=mgrid

    ORDER BY id;

id  mgrid       name        hierarchy

=================================================

1   null        Kim     /Kim

2   null        Moy     /Moy

3   1       Jonas       /Kim/Jonas

4   1       Smith       /Kim/Smith

5   2       Verma       /Moy/Verma

6   2       Foster      /Moy/Foster

7   6       Brown       /Moy/Foster/Brown