How to Use SYS_CONNECT_BY_PATH in Oracle

SYS_CONNECT_BY_PATH :

Syntax:

SYS_CONNECT_BY_PATH is a function which is used in hierarchical queries to get the path for the current node starting from Parent node.

SELECT SYS_CONNECT_BY_PATH ( column , char)

FROM table_name

START WITH <root_node>

CONNECt BY NOCYCLE PRIOR < child_node_col> = <Parent_node>

Column -> this is the first parameter which indicates the scalar value for the node .

Char -> indicated the delimiter which can be used as node value separator.

START WITH  -> specifies the root row(s) of the hierarchy.

CONNECT BY--> specifies the relationship between parent rows and child rows .

Nocycle -> By Specifying this clause, Oracle will return the rows despite of the loop

<CONNECT_BY_ISCYCLE can be used to see if there is any loop , explained in other section>

Let’s take an example:

Sample data:

ID	FIRST_NAME			LAST_NAME		PARENT
01	Jason			Martin		02
02	Alison			Mathews
03	James			Smith		02
04	Celia			Rice		02
05	Robert			Black		03
06	Linda			Green		03
07	David			Larry		03
08	James			Cat		04
Hierarchy of Above Data
Alison					Parent (level1)
	Jason				Level2 (under level1)
	James				Level3 (under level2)
		Robert
		Linda
		David
	Celia
		James

Desired Output

/Parent

/Parent/Level1

/Parent/Level1/Level2

SQL Query :

SELECT first_name, id, SYS_CONNECT_BY_PATH (first_name, '/') root

      FROM employee

START WITH parent IS NULL

CONNECT BY NOCYCLE PRIOR id = parent

FIRST_NAME	ID	ROOT
Alison	02	/Alison
Jason	01	/Alison/Jason
James	03	/Alison/James
Robert	05	/Alison/James/Robert
Linda	06	/Alison/James/Linda
David	07	/Alison/James/David
Celia	04	/Alison/Celia
James	08	/Alison/Celia/James

Now Lets discuss  NOCYCLE and IS_CYCLE in detail:

For this, lets modify the above data :

ID	FIRST_NAME	LAST_NAME	PARENT
01	Jason	Martin	02
02	Alison	Mathews	03
03	James	Smith	02
04	Celia	Rice	02
05	Robert	Black	03
06	Linda	Green	03
07	David	Larry	03
08	James	Cat	04

For ID 2,3 , there is a cycle . Both are bosses of each other :)

Lets run the above query :

    SELECT first_name

         , id

         , LEVEL

         , SYS_CONNECT_BY_PATH (first_name, '/') root

      FROM employee

START WITH id = '02'

CONNECT BY NOCYCLE PRIOR id = parent

Error -> ORA-01436 Connect By Loop in user data

Let’s check the cycle:

  SELECT first_name

         , id

         , LEVEL

         , CONNECT_BY_ISCYCLE "Cycle"

         , SYS_CONNECT_BY_PATH (first_name, '/') root

      FROM employee

START WITH id = '02'

CONNECT BY NOCYCLE PRIOR id = parent

FIRST_NAME	ID	LEVEL	Cycle	Root
Alison	2	1	0	/Alison
Jason	1	2	0	/Alison/Jason
James	3	2	1	/Alison/James
Robert	5	3	0	/Alison/James/Robert
Linda	6	3	0	/Alison/James/Linda
David	7	3	0	/Alison/James/David
Celia	4	2	0	/Alison/Celia
James	8	3	0	/Alison/Celia/James