Creating recursive SQL by using common table expressions

Queries that use recursion are useful in applications like bill-of-materials applications, network planning applications, and reservation systems.

About this task

You can use common table expressions to create recursive SQL If a fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive common table expression.

Recursive common table expressions must follow these rules:
  • The first fullselect of the first union (the initialization fullselect) must not include a reference to the common table expression.
  • Each fullselect that is part of the recursion cycle must:
    • Start with SELECT or SELECT ALL. SELECT DISTINCT is not allowed.
    • Include only one reference to the common table expression that is part of the recursion cycle in its FROM clause.
    • Not include aggregate functions, a GROUP BY clause, or a HAVING clause.
  • The column names must be specified after the table name of the common table expression.
  • The data type, length, and CCSID of each column from the common table expression must match the data type, length, and CCSID of each corresponding column in the iterative fullselect.
  • If you use the UNION keyword, specify UNION ALL instead of UNION.
  • You cannot specify INTERSECT or EXCEPT.
  • Outer joins must not be part of any recursion cycle.
  • A subquery must not be part of any recursion cycle.
Important: You should be careful to avoid an infinite loop when you use a recursive common table expression. Db2 issues a warning if one of the following items is not found in the iterative fullselect of a recursive common table expression:
  • An integer column that increments by a constant
  • A predicate in the WHERE clause in the form of counter_column < constant or counter_column < :host variable

See Examples of recursive common table expressions for examples of bill-of-materials applications that use recursive common table expressions.