Oracle Hierarchy Inquiry Connect by usage

If the table contains hierarchical data, you can use hierarchical query clauses to select row-level order.

1. Hierarchical query clause syntax

Hierarchical query clause syntax:

{CONNECT by [nocycle] condition [and condition] ... [START with condition]
| START with Condition CONNECT by [nocycle] condition [and condition] ...
}

START with: Specifies the node row of the hierarchy.

CONNECT by: Specifies the relationship of the parent row of the hierarchy to the child rows.

• The nocycle parameter instructs the Oracle database query to return rows, even if connect by has loops in the data. Typically used with connect_by_iscycle pseudo-columns to see if a row contains loops.
• In a hierarchical query, the conditions of an expression must be qualified with the prior operator to query the parent row, for example:

... PRIOR expr = expr
Or
... expr = PRIOR expr

If the condition of connect by is a compound condition, only one condition prior operator is required, however there can be multiple prior conditions. For example:

CONNECT by Last_Name! = ' King ' and PRIOR employee_id = manager_id ...
CONNECT by PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...

Prior is a unary operator and a unary +-arithmetic operator with the same precedence. Prior calculates the parent row of the current row immediately, based on the expression in the hierarchy query.

Prior must be used with the equality operator that compares column values (the PRIOD keyword can be any side of the equality operator).

The CONNECT by condition and the priod expression form an unrelated subquery structure. Therefore, Currval and nextval are invalid prior expressions, so the prior expression cannot be used for the query sequence.

Hierarchical queries can be further refined by using the Connect_by_root operator to qualify columns in the query list. This operator extends the ability to query the function of the connect by [PRIOR] condition, not only immediately returning the parent row, but also returning all row root node rows in the hierarchy.

2. Hierarchical query pseudo-columns

Hierarchical query pseudo-columns are valid only in hierarchical queries, and hierarchical query pseudo-columns:

2.1.connect_by_iscycle Pseudo-Column

If the current row has a child row and the child row is the ancestor row of the current row, Connect_by_iscycle returns 1, otherwise 0 is returned.

You can specify connect_by_iscycle only if the nocycle parameter is specified in the Connect by clause. Because the connect by has cyclic data, nocycle can cause Oracle to return query results, otherwise the query will fail.

2.2.connect_by_isleaf Pseudo-Column

If the current row is a leaf node of the CONNECT by condition definition tree, the connect_by_isleaf pseudo-column returns 1, otherwise 0 is returned. This information also indicates whether a given row can be further expanded to show more layers.

2.3.LEVEL Pseudo-Column

Each row returned by the hierarchical query, followed by the node row level pseudo-column, returns 1, followed by the node's child node row level for example 2, and so on. The row with the node is the highest row of the inverted tree. The child node row is any non-heel node row. The parent node row is any row that has child nodes. A leaf node row is any row that has no child nodes.

3.examples3.1.connect by Example

Query the superiors of all employees.

Sql> Select E.empno, E.ename, e.mgr from emp e connect by prior e.empno = E.mgr;

EMPNO ename MGR
----- ---------- -----
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698
7900 JAMES 7698
7844 TURNER 7698
7654 MARTIN 7698
7521 WARD 7698
7934 MILLER 7782
7876 ADAMS 7788
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902

........

3.2.LEVEL Example

Use the Level virtual column to display the parent row's hierarchy of child rows

Sql> Select E.empno, E.ename, e.mgr,level from emp e connect by prior e.empno = E.mgr;

EMPNO ename MGR Level
----- ---------- ----- ----------
7788 SCOTT 7566 1
7876 ADAMS 7788 2
7902 FORD 7566 1
7369 SMITH 7902 2
7499 ALLEN 7698 1
7900 JAMES 7698 1
7844 TURNER 7698 1
7654 MARTIN 7698 1
7521 WARD 7698 1
7934 MILLER 7782 1
7876 ADAMS 7788 1
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3

............

3.3.START with Examples

Starting with Employee King, check out the superiors of all employees

Sql> Select E.empno, E.ename, e.mgr, Level
2 from EMP E
3 Connect by Prior E.empno = E.mgr
4 start with e.ename = ' KING ';

EMPNO ename MGR Level
----- ---------- ----- ----------
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3

3.4.NOCYCLE Examples

Create a connect by loop data that designates the employee Scott as the employee King's superior, and a dead loop occurs.

To create circular data:

sql> update emp e set e.mgr = ' 7788 ' where e.ename = ' KING ';

Query the superiors of an employee who starts with King:

Sql> Select E.empno, E.ename, e.mgr, Level
2 from EMP E
3 Connect by Prior E.empno = E.mgr
4 start with e.ename = ' KING ';

Select E.empno, E.ename, e.mgr, Level
From EMP E
Connect by Prior E.empno = E.mgr
Start with e.ename = ' KING '

ORA-01436: CONNECT by loop in user data

Using the nocycle parameter, query the employee's superiors starting with King:

Sql> Select E.empno, E.ename, e.mgr, Level, connect_by_iscycle "CYCLE"
2 from EMP E
3 Connect by nocycle prior e.empno = E.mgr
4 start with e.ename = ' KING ';

EMPNO ename MGR Level CYCLE
----- ---------- ----- ---------- ----------
7839 KING 7788 1 0
7566 JONES 7839 2 0
7788 SCOTT 7566 3 1
7876 ADAMS 7788 4 0
7902 FORD 7566 3 0
7369 SMITH 7902 4 0
7698 BLAKE 7839 2 0
7499 ALLEN 7698 3 0
7521 WARD 7698 3 0
7654 MARTIN 7698 3 0
7844 TURNER 7698 3 0
7900 JAMES 7698 3 0
7782 CLARK 7839 2 0
7934 MILLER 7782 3 0

Oracle Hierarchy Inquiry Connect by usage