This operator can be applied to columns and it returns the parent row or root row values for that column. This operator may be used in the SELECT list, Where clause and ORDER BY clause. When using the CONNECT BY clause some column operators become available.
The following example shows how to execute a hierarchical query with CONNECT_BY_ROOT operator.
-- Executing a hierarchical query with CONNECT_BY_ROOT operator
SELECT id, mgrid, name, CONNECT_BY_ROOT id
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name connect_by_root id
==========================================================
1 null Kim 1
2 null Moy 2
3 1 Jonas 1
4 1 Smith 1
5 2 Verma 2
6 2 Foster 2
7 6 Brown 2
This operator may be applied to a column; it will return the parent node value for that column. For a root node, the operator will return the NULL value if it is applied to a column. This operator may be used in the SELECT list, WHERE clause, ORDER BY clause and also in the CONNECT BY clause.
The following example shows how to execute a hierarchical query with PRIOR operator.
-- Executing a hierarchical query with PRIOR operator
SELECT id, mgrid, name, PRIOR id as "prior_id"
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name prior_id
========================================
1 null Kim null
2 null Moy null
3 1 Jonas 1
4 1 Smith 1
5 2 Verma 2
6 2 Foster 2
7 6 Brown 6