Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Functions Available When Using the CONNECT BY Clause

Description

The SYS_CONNECT_BY_PATH function returns the branch of the node in the hierarchy. It returns a string that represents the concatenation of all the values obtained by evaluating the scalar expression for all the parents of a row, including that row, separated by the separator character, ordered ascending by level.

This function may be used in the SELECT list, WHERE clause and ORDER BY clause.

Syntax

SYS_CONNECT_BY_PATH (column_name, separator_char)

Example

The following example shows how to execute a hierarchical query with SYS_CONNECT_BY_PATH function.

-- 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