correlation-clause

Each correlation-name in a correlation-clause defines a designator for the immediately preceding result table, which can be used to qualify references to the columns of the table.

The preceding result table is one of the following objects:

  • A table
  • A view
  • A nested table expression
  • A table function
  • A data-change table reference
  • A collection-derived table

Syntax for correlation-clause

Read syntax diagramSkip visual syntax diagramAScorrelation-name(, new-column-name)

Description for correlation-clause

new-column-name is an SQL identifier of 128 UTF-8 bytes or less. Using new-column-name to list and rename the columns is optional. A correlation name must be specified for nested table expressions and references to table functions.

If correlation-name is not specified for a data-change table reference, the correlation name is the name of the target table or view of the SQL data change statement. Otherwise, the correlation name is correlation-name.

If a new-column-name list is specified in correlation-clause, the number of names must be the same as the number of columns in the corresponding object. Each name must be unique and unqualified. If columns are added to an underlying table of a table-reference, the number of columns in the result of the table-reference no longer matches the number of names in its correlation-clause. Therefore, when a rebind of a package containing the query in question is attempted, Db2 returns an error and the rebind fails. At that point, change the correlation-clause of the embedded SQL statement in the application program so that the number of names matches the number of columns. Then prepare the modified program again.

An exposed name is a correlation-name or a table-name or view name that is not followed by a correlation-name. The exposed names in a FROM clause must be unique. Any qualified reference to a column for a table, view, nested table expression, table function, data-change table reference, or collection-derived table must use the exposed name.

If the same table name or view name is specified twice, at least one specification must be followed by a correlation-name. The correlation-name is used to qualify references to the columns of the table or view.

When a correlation-name is specified, column names can also be specified to give names to the columns of the table-name, view-name, nested-table-expression, table-function, data-change-table-reference, or collection-derived-table. If a column list is specified, there must be a name in the column list for each column in the table or view and for each result column in the table-function, data-change-table-reference, or collection-derived-table.

For more information, see Correlation names.

In general, nested-table-expression, table-function, data-change-table-reference, or collection-derived-table can be specified in any FROM clause. Columns from the nested-table-expression, table-function, data-change-table-reference, or collection-derived-table can be referenced in the SELECT list and in the rest of the subselect using a correlation name. The scope of this correlation name is the same as correlation names for other table or view names in the FROM clause.