CONNECT BY Clause

The CONNECT BY clause is an extension to the ANSI/ISO standard for SQL. This syntax fragment is part of the Hierarchical Clause.

Usage

If you include the START WITH clause, the search condition that it specifies is applied in producing the first intermediate result set for the hierarchical query. This consists of the rows of the table specified in the FROM clause for which the START WITH condition is true.

If the START WITH clause is omitted, no START WITH condition is available as a filter, and the first intermediate result set is the entire set of rows in the table that the FROM clause specifies.

The CONNECT BY clause produces successive intermediate result sets by applying the CONNECT BY search condition until this recursive process terminates when an iteration yields an empty result set.

The NOCYCLE Keyword

Rows returned by recursive queries of the CONNECT BY clause must be part of a simple hierarchy. SELECT statements that include the Hierarchical clause fail with an error if the query returns a row that is both the ancestor and the descendant of another node. This topology is called a cycle.

You can include the NOCYCLE keyword between the CONNECT BY keywords and the condition specification of the CONNECT BY clause to filter out any rows that would otherwise cause the hierarchical query to fail with error -26079 because of a cycle in an intermediate result set.

UPDATE employee SET mgrid = 5 WHERE name = 'Urbassek';

After the hierarchical data set has been modified by the UPDATE statement above, the following query (which omits the NOCYCLE keyword) fails:

SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
FROM employee
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid;

Error -26079 is issued when the last CONNECT BY step detects that employee McKeough is part of a loop:

      empid name             mgrid   leaf 

         16 Goyal               17      0
         14 Scott               16      1
         12 Henry               16      0
          9 Shoeman             12      1
          8 Smith               12      1
          7 O'Neil              12      1
         11 Zander              16      0
          6 Barnes              11      1
          5 McKeough            11      0

26079: CONNECT BY query resulted in a loop/cycle.
Error in line 8
Near character position 28

You can include the NOCYCLE keyword between the CONNECT BY keywords and the condition specification of the CONNECT BY clause to filter out any rows that would otherwise cause the hierarchical query to fail with error -26079 because of a cycle in an intermediate result set. The following query differs from the query that failed by including the CONNECT_BY_ISCYCLE pseudocolumn in the Projection clause, and by including the NOCYCLE keyword in the CONNECT BY clause.

SELECT empid, name, mgrid, CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle
FROM employee
START WITH name = 'Goyal'
CONNECT BY NOCYCLE PRIOR empid = mgrid;

      empid name             mgrid   leaf  cycle 

         16 Goyal               17      0      0
         14 Scott               16      1      0
         12 Henry               16      0      0
          9 Shoeman             12      1      0
          8 Smith               12      1      0
          7 O'Neil              12      1      0
         11 Zander              16      0      0
          6 Barnes              11      1      0
          5 McKeough            11      0      0
         17 Urbassek             5      0      1
         15 Mills               17      0      0
         13 Aaron               15      1      0
         10 Monroe              15      0      0
          4 Lindsay             10      1      0
          3 Kim                 10      1      0
          2 Hall                10      1      0
          1 Jones               10      1      0

17 row(s) retrieved.

Because the NOCYCLE keyword enabled the CONNECT BY clause to continue processing after the cycle was detected, Urbassek was returned from the CONNECT BY step that had failed in the previous example, and processing continued until all of the rows in the data set had been returned. In the output display above, leaf is an alias for the CONNECT_BY_ISLEAF pseudocolumn, and cycle is an alias for the CONNECT_BY_ISCYCLE pseudocolumn, with both aliases declared in the Projection clause. In these results, Urbassek is marked in the cycle column as the cause of the loop.

The result set above implies that the cycle can be removed from the employee table by changing the mgrid value in the row that had identified McKeough as the manager of Urbassek:

UPDATE employee SET mgrid = NULL WHERE empid = 17;