Hierarchical queries

Connect-by recursion uses the same subquery for the seed (start) and the recursive step (connect). This combination provides a concise method of representing recursions such as bills-of-material, reports-to-chains, or email threads.

Connect-by recursion returns an error if a cycle occurs. A cycle occurs when a row produces itself, either directly or indirectly. Using the optional CONNECT BY NOCYCLE clause, the recursion can be directed to ignore the duplicated row, thus avoiding both the cycle and the error.

subselect

Hierarchical query support includes the following extensions to the subselect.

• The subselect includes a hierarchical-query-clause
• The clauses of the subselect are processed in the following sequence:
  1. FROM clause
  2. hierarchical-query-clause
  3. WHERE clause
  4. GROUP BY clause
  5. HAVING clause
  6. SELECT clause
  7. ORDER BY clause
  8. FETCH FIRST clause
• If the subselect includes a hierarchical-query-clause, special rules apply for the order of processing the predicates in the WHERE clause. The search-condition is factored into predicates along with its AND conditions (conjunction). If a predicate is an implicit join predicate (that is, it references more than one table in the FROM clause), the predicate is applied before the hierarchical-query-clause. Any predicate referencing at most one table in the FROM clause is applied to the intermediate result table of the hierarchical-query-clause.

  A hierarchical query involving joins should be written using explicit joined tables with an ON clause to avoid confusion about the application of WHERE clause predicates.

• The ORDER SIBLINGS BY clause can be specified if the subselect includes a hierarchical-query-clause. This clause specifies that the ordering applies only to siblings within the hierarchies.