SQL Reference

common-table-expression

>>-table-name--+-------------------------------------+---AS--(--fullselect--)-->
               |    .-,------------------.           |
               |    V                    |     (1)   |
               '-(-----+--------------+--+---)-------'
                       '-column-name--'
 
>--------------------------------------------------------------><
 

Notes:

  1. If a common table expression is recursive, or if the fullselect results in duplicate column names, column names must be specified.

A common table expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows. Multiple common table expressions can be specified following the single WITH keyword. Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.

If a list of columns is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If these column names are not specified, the names are derived from the select list of the fullselect used to define the common table expression.

The table-name of a common table expression must be different from any other common table expression table-name in the same statement (SQLSTATE 42726). If the common table expression is specified in an INSERT statement the table-name cannot be the same as the table or view name that is the object of the insert (SQLSTATE 42726). A common table expression table-name can be specified as a table name in any FROM clause throughout the fullselect. A table-name of a common table expression overrides any existing table, view or alias (in the catalog) with the same qualified name.

If more than one common table expression is defined in the same statement, cyclic references between the common table expressions are not permitted (SQLSTATE 42835). A cyclic reference occurs when two common table expressions dt1 and dt2 are created such that dt1 refers to dt2 and dt2 refers to dt1.

The common table expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements.

A common table expression can be used:

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. Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning. For an example, see Appendix M, Recursion Example: Bill of Materials.

The following must be true of a recursive common table expression:

When developing recursive common table expressions, remember that an infinite recursion cycle (loop) can be created. Check that recursion cycles will terminate. This is especially important if the data involved is cyclic. A recursive common table expression is expected to include a predicate that will prevent an infinite loop. The recursive common table expression is expected to include:

A warning is issued if this syntax is not found in the recursive common table expression (SQLSTATE 01605).


[ Top of Page | Previous Page | Next Page ]