common-table-expression

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.

Notes:

¹ If a common table expression is recursive, or if the fullselect results in duplicate column names, column names must be specified.
² For Netezza compatibility, the WITH clause is supported in common table expressions. This SQL compatibility enhancement is only available in Db2® Version 11.5 Mod Pack 2 and later versions.

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.

If the fullselect of a common table expression contains a data-change-table-reference in the FROM clause, the common table expression is said to modify data. A common table expression that modifies data is always evaluated when the statement is processed, regardless of whether the common table expression is used anywhere else in the statement. If there is at least one common table expression that reads or modifies data, all common table expressions are processed in the order in which they occur, and each common table expression that reads or modifies data is completely executed, including all constraints and triggers, before any subsequent common table expressions are executed.

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

A common table expression can be used in the following situations:

In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used)
To enable grouping by a column that is derived from a scalar subselect or function that is not deterministic or has external action
When the required result table is based on host variables
When the same result table must be shared in a fullselect
When the result must be derived using recursion
When multiple SQL data change statements must be processed within the query

If the 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.

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

Each fullselect that is part of the recursion cycle must start with SELECT or SELECT ALL. Use of SELECT DISTINCT is not allowed (SQLSTATE 42925). Furthermore, the unions must use UNION ALL (SQLSTATE 42925).
The column names must be specified following the table-name of the common table expression (SQLSTATE 42908).
The first fullselect of the first union (the initialization fullselect) must not include a reference to any column of the common table expression in any FROM clause (SQLSTATE 42836).
If a column name of the common table expression is referred to in the iterative fullselect, the data type, length, and code page for the column are determined based on the initialization fullselect. The corresponding column in the iterative fullselect must have the same data type and length as the data type and length determined based on the initialization fullselect and the code page must match (SQLSTATE 42825). However, for character string types, the length of the two data types can differ. In this case, the column in the iterative fullselect must have a length that will always be assignable to the length determined from the initialization fullselect.
Each fullselect that is part of the recursion cycle must not include any aggregate functions, group-by-clauses, or having-clauses (SQLSTATE 42836).
The FROM clauses of these fullselects can include at most one reference to a common table expression that is part of a recursion cycle (SQLSTATE 42836).
The iterative fullselect and the overall recursive fullselect must not include an order-by-clause (SQLSTATE 42836).
Subqueries (scalar or quantified) must not be part of any recursion cycles (SQLSTATE 42836).

When developing recursive common table expressions, remember that an infinite recursion cycle (loop) can be created. Check that recursion cycles will stop. 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:

In the iterative fullselect, an integer column incremented by a constant.
A predicate in the where clause of the iterative fullselect in the form "counter_col < constant" or "counter _col < :hostvar".

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