table-reference

A table-reference specifies a result table as either a table or view, or an intermediate table.

table-reference:


>>-+-single-table-reference------+-----------------------------><
   +-nested-table-expression-----+   
   +-data-change-table-reference-+   
   +-table-function-reference----+   
   +-table-locator-reference-----+   
   +-xmltable-expression---------+   
   '-joined-table----------------'

single-table-reference:


                   .----------------------------------.   
                   V                                  |   
>>-+-table-name-+----+------------------------------+-+--------->
   '-view-name--'    |                          (1) |     
                     '-| period-specification |-----'     

>--+--------------------+--------------------------------------><
   '-correlation-clause-'

Notes:

period-specification cannot be specified with view-name

period-specification:


                      (1)       
>>-FOR--+-SYSTEM_TIME-------+----------------------------------->
        |               (2) |   
        '-BUSINESS_TIME-----'   

>--+-AS OF--value-----------------+----------------------------><
   +-FROM--value1--TO--value2-----+   
   '-BETWEEN--value1--AND--value2-'

Notes:

AS OF TIMESTAMP can be specified as an alternative and is treated as if FOR SYSTEM_TIME AS OF had been specified.
SYSTEM_TIME and BUSINESS_TIME cannot be specified more than one time per table.

nested-table-expression:


>>-+-------+--(fullselect)--correlation-clause-----------------><
   '-TABLE-'

data-change-table-reference:


>>-+-FINAL TABLE--(INSERT statement)---------------+------------>
   +-+-FINAL-+--TABLE--(searched UPDATE statement)-+   
   | '-OLD---'                                     |   
   +-OLD TABLE--(searched DELETE statement)--------+   
   '-FINAL TABLE--(MERGE statement)----------------'   

>--+--------------------+--------------------------------------><
   '-correlation-clause-'

table-function-reference:

>>-TABLE-(function-name(-+--------------------------------------+-)-+------------------------------+-)-->
                         | .-,--------------------------------. |   '-table-UDF-cardinality-clause-'     
                         | V                                  | |                                        
                         '---+-expression-------------------+-+-'                                        
                             '-TABLE--transition-table-name-'                                            

>--+--------------------+--------------------------------------><
   '-correlation-clause-'

table-UDF-cardinality-clause:


>>-+-CARDINALITY--integer-constant------------+----------------><
   '-CARDINALITY MULTIPLIER--numeric-constant-'

table-locator-reference:


>>-TABLE--(--table-locator-variable--LIKE--table-name--)-------->

>--+------------------+----------------------------------------><
   '-correlation-name-'

xmltable-expression:


>>-xmltable-function--correlation clause-----------------------><

A table-reference specifies an intermediate result table.

If a single-table-reference is specified, the intermediate result table is simply that table. If a period-specification is specified, the intermediate result table consists of the rows of the temporal table where the period matches the specification.
If a nested-table-expression is specified, the result table is the result of the specified fullselect. The columns of the result do not need unique names, but a column with a non-unique name cannot be explicitly referenced.
If a data-change-table-reference is specified, the intermediate result table is the set of rows that are directly affected by the data change statement.
If a table-function-reference is specified, the intermediate result table is the set of rows that are returned by the table function.
If a table-locator-reference is specified, the host variable represents the intermediate result table. The intermediate result table has the same structure as the table identified in table-name.
If an xmltable-expression is specified, the intermediate result table is the set of rows that are returned by the XMLTABLE function.
If a joined-table is specified, the intermediate result table is the result of one or more join operations. For more information, see joined-table.

Each table-name or view-name specified in every FROM clause of the same SQL statement must identify a table or view that exists at the same DB2® subsystem. If a FROM clause is specified in a subquery of a basic predicate, a view that includes GROUP BY or HAVING must not be identified.

A table-reference must not identify a table that was implicitly created for an XML column.

table-locator-variable

table-locator-variable must specify a variable with a table locator type. The only way to assign a value to a table locator is to pass the old or new transition table of a trigger to a user-defined function or stored procedure. A table locator host variable must not have a null indicator. A table locator variable must not be a parameter marker. In addition, a table locator can be used only in a manipulative SQL statement.

nested-table-expression

A fullselect in parentheses is called a nested table expression. If a nested table expression is specified, the result table is the result of that nested-table-expression. The columns of the result do not need unique names, but a column with a non-unique name cannot be referenced. At any time, the table consists of the rows that would result if the fullselect were executed.

table-function-reference

If a function-name is specified, the result table is the set of rows returned by the table function.

Start of change expression must not contain a scalar fullselect, a function, or a reference to a column. End of change

Each function-name, together with the types of its arguments, must resolve to a table function that exists at the same DB2 subsystem. An algorithm called function resolution, which is described in Function resolution, uses the function name and the arguments to determine the exact function to use. Unless given column names in the correlation-clause, the column names for a table function are those specified on the RETURNS clause of the CREATE FUNCTION statement. This is analogous to the column names of a table, which are defined in the CREATE TABLE statement.

Start of change If a column mask is used to mask the column values in the final result table, and if the result of the table function is used to derive the final result table, the column mask cannot be applied to a column that is specified in the argument of the table function. End of change

table-UDF-cardinality-clause

The table-UDF-cardinality-clause can be specified to each user-defined table function reference within the table spec of the FROM clause in a subselect. This option indicates the expected number of rows to be returned only for the SELECT statement that contains it.

CARDINALITY integer-constant specifies an estimate of the expected number of rows returned by the reference to the user-defined function. The value of integer-constant must range from 0 to 2147483647.

The value set in the CARDINALITY field of SYSIBM.SYSROUTINES for the table function name is used as the reference cardinality value. The product of the specified CARDINALITY MULTIPLIER numeric-constant and the reference cardinality value are used by DB2 as the expected number of rows returned by the table function reference.

In this case, the numeric-constant can be in the integer, decimal, or floating-point format. The value must be greater than or equal to zero. If the decimal number notation is used, the number of digits can be up to 31. An integer value is treated as a decimal number with no fraction. The maximum value allowed for a floating-point number is about 7.237E + 75. If no value has been set in the CARDINALITY field of SYSIBM.SYSROUTINES, its default value is used as the reference cardinality value. If zero is specified or the computed cardinality is less than 1, DB2 assumes that the cardinality of the reference to the user-defined table function is 1.

Only a numeric constant can follow the keyword CARDINALITY or CARDINALITY MULTIPLIER. No host variable or parameter marker is allowed in a cardinality option. Specifying a cardinality option in a table function reference does not change the corresponding CARDINALITY field in SYSIBM.SYSROUTINES. The CARDINALITY field value in SYSIBM.SYSROUTINES can be initialized by the CARDINALITY option in the CREATE FUNCTION (external table) statement when a user-defined table function is created. It can be changed by the CARDINALITY option in the ALTER FUNCTION statement or by a direct update operation to SYSIBM.SYSROUTINES.

data-change-table-reference

A data-change-table-reference clause specifies an intermediate result table. This table is based on the rows that are directly changed by the SQL data change statement that is included in the clause. A data-change-table-reference can only be specified as the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement and that fullselect must be in a subselect, or a SELECT INTO statement. A data-change-table-reference in a SELECT statement of a cursor makes the cursor read only. The target table or view of the SQL data change statement is a table or view that is reference in the query. The privileges that are held by the authorization ID of the statement must include the SELECT privilege on that target table or view. Start of change

The encoding scheme of the result table of the SELECT must be the same as the encoding scheme of the target table or view of the data-change-table-reference. End of change

Start of change If row access control is enforced for the target of the data change statement, the rows in the intermediate result table already satisfy the rules that are specified in the enabled row permissions. If column access control is enforced for the target of the data change statement, the enabled column masks are applied to the outermost select list. See select-clause for more information. If an INCLUDE clause is specified as part of the SQL data change statement, and these additional columns appear in the outermost select list, the column values must not be derived from columns for which column masks are defined. End of change

Expressions in the select list of a view in a table reference can only be selected if OLD TABLE is specified or if the expression does not include any of the following objects:

a function that is defined to read or modify SQL data
a function that is defined as not deterministic or has an external action
a NEXT VALUE expression for a sequence

FINAL TABLE

Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement as they appear at the completion of the SQL data change statement. If there are AFTER triggers that result in further operations on the table that is the target of the SQL data change statement, an error is returned. If the target of the SQL data change statement is a view that is defined with an INSTEAD OF trigger for the type of data change, an error is returned.

OLD TABLE

The rows of the intermediate result table represent the set of affected rows as they exist prior to the application of the SQL data change statement.

INSERT statement

Specifies an INSERT statement as described in INSERT. A fullselect in the INSERT statement cannot contain correlated references to columns that are outside of the fullselect of the INSERT statement. The target of the INSERT statement must be a base table, a view that is defined with the WITH CASCADED CHECK clause, or a view where the view definition has no WHERE clause. If there are input variables elsewhere in the fullselect, the INSERT statement cannot be a multiple row not atomic insert, or a multiple row atomic insert that specifies the USING DESCRIPTOR clause.

MERGE statement

Specifies a MERGE statement as described in MERGE. The MERGE statement must conform to the following rules:

A table reference must not contain correlated references to columns that are outside of the table reference in the MERGE statement.
A referenced view must be defined using the WITH CASCADED CHECK OPTION clause.
The target table or view of the MERGE statement must not have a column with a ROWID, LOB, or XML data type.
AFTER triggers that result in further operations on the target table cannot exist on the target table.

searched UPDATE statement

Specifies a searched UPDATE statement as described in UPDATE. A WHERE clause or a SET clause in the UPDATE statement cannot contain correlated referenced to columns that are outside of the UPDATE statement. The target of the UPDATE statement must be a base table, a symmetric view, or a view where the view definition has no WHERE clause.

If the searched UPDATE statement is used in the SELECT statement and the UPDATE statement references a view, the view must be defined using the WITH CASCADED CHECK OPTION clause.

Start of change A searched UPDATE statement in a SELECT statement will not clear the AREO* status of a table. End of change

AFTER triggers that result in further operations on the target table cannot exist on the target table.

searched DELETE statement

Specifies a searched DELETE statement as described in DELETE. A WHERE clause in the DELETE statement cannot contain correlated references to columns that are outside of the DELETE statement. The target of the DELETE statement must be a base table, a symmetric view, or a view where the view definition has no WHERE clause.

If the searched DELETE statement is used in the SELECT statement and the DELETE statement references a view, the view must be defined using the WITH CASCADED CHECK OPTION clause.

AFTER triggers that result in further operations on the target table cannot exist on the target table.

Start of change The content of the intermediate result table for a table reference that contains an SQL data change statement is determined when the cursor is opened. The intermediate result table includes a column for each of the columns of the target table (including implicitly hidden columns) or view. All of the columns of the target table or view of an SQL data change statement are accessible by using the names of the columns from the target table or view unless the columns are renamed by using the correlation clause. If an INCLUDE clause is specified as part of the SQL data change statement, the intermediate result table will contain these additional columns. End of change

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. See correlation-clause for more information.

The exposed names of all table references in the FROM clause should be unique. An exposed name is considered to be any of the following names:

A correlation-name
A table-name that is not followed by a correlation-name
A view-name that is not followed by a correlation-name
A function-name that is not followed by a correlation-name
The table name that is specified after LIKE when a table-locator is not followed by a correlation-name
The target table or view name for a data-change-table-reference that is not followed by a correlation-name
An alias-name that is not followed by a correlation-name
A synonym-name that is not followed by a correlation-name

Any qualified reference to a column must use the exposed name. If the same name is specified twice, at least one specification should 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 reference. If the correlation-clause does not include column names, the exposed column names are determined as follows:

Column names of the referenced table or view when the table-reference is table-name, view-name, alias-name, or synonym-name
Column names specified in the RETURNS clause of the CREATE FUNCTION statement when the table-reference is a function-name reference
Column names of the table referenced after LIKE when the table-reference is a table-locator
Column names from the target table of the data change statement, along with any defined INCLUDE columns, when the table-reference is a data-change-table-reference

Otherwise, there are no exposed names for the columns of that table reference. End of change

xmltable-expression

Specifies an invocation of the built-in XMLTABLE function. See XMLTABLE for more information.

Start of change If a column mask is used to mask the column values in the final result table, and if the result of the XMLTABLE function is used to derive the final result table, the column mask cannot be applied to a column that is specified in the PASSING clause of the XMLTABLE function. End of change

joined-table

If a joined-table is specified, the result table is the result of one or more join operations as explained in joined-table.

period-specification

Specifies that a period specification applies to the table-reference. The same period name (SYSTEM_TIME or BUSINESS_TIME) must not be specified more than one time for the same table. The table-reference must not be a view.

The rows of the table reference are derived by application of the specified period specification.

If the table is a system-period temporal table and a period-specification for the SYSTEM_TIME period is not specified, the table reference includes all current rows of the table and does not include any historical rows of the table.

If the table is an application-period temporal table and a period-specification for the BUSINESS_TIME period is not specified, the table reference includes all rows of the table.

If the table is a bitemporal table and a period-specification is not specified for either SYSTEM_TIME or BUSINESS_TIME, the table reference includes all current rows of the table and does not include any historical rows of the table.

FOR SYSTEM_TIME

Specifies that the SYSTEM_TIME period is used for the period-specification. The table must be a system-period temporal table.

FOR BUSINESS_TIME

Specifies that the BUSINESS_TIME period is used for the period-specification. The table must be an application-period temporal table.

AS OF value

Specifies that the table-reference includes each row for which the begin value for the specified period is less than or equal to value and the end value for the period is greater than value.

value

Specifies an expression that returns a value of a built-in data type. The result of the expression must be comparable to the data type of the columns of the specified period according to the comparison rules specified in Assignment and comparison.

Start of change The expression must not have a timestamp precision that is greater than the precision of the columns for the period. End of change

Start of change If the begin and end columns of the period are defined as TIMESTAMP WITHOUT TIME ZONE, the expression must not return a value of a timestamp with a time zone. End of change

The expression can contain any of the following supported operands:

A constant
A special register
A variable
A built-in scalar function whose arguments are supported operands
A CAST specification where the cast operand is a supported operand
An expression that uses arithmetic operators and operands

FROM value1 TO value2

Specifies that the table-reference includes rows that exist for the period that is specified from value1 up to value2.

A row is included in table-reference if the start value for the period in the row is less than value2 and the end value for the period in the row is greater than value1. The table-referencecontains zero rows if value1 is greater than or equal to value2. If value1 or value2 is the null value, the table-reference is an empty table.

value1 or value2

Start of change The expression must not have a timestamp precision that is greater than the precision of the columns for the period. End of change

Start of change If the begin and end columns of the period are defined as TIMESTAMP WITHOUT TIME ZONE, the expression must not return a value of a timestamp with a time zone. End of change

The expression can contain any of the following supported operands:

A constant
A special register
A variable
A built-in scalar function whose arguments are supported operands
A CAST specification where the cast operand is a supported operand
An expression that uses arithmetic operators and operands

BETWEEN value1 AND value2

Specifies that the table-reference includes rows in which the specified period overlaps at any point in time between value1 and value2.

A row is included in the table-reference if the start value for the period in the row is less than or equal to value2 and the end value for the period in the row is greater than value1. The table reference contains zero rows if value1 is greater than value2. If value1 = value2, the expression is equivalent to AS OF value1. If value1 or value2 is the null value, the table reference is an empty table.

value1 or value2

Start of change The expression must not have a timestamp precision that is greater than the precision of the columns for the period. End of change

Start of change If the begin and end columns of the period are defined as TIMESTAMP WITHOUT TIME ZONE, the expression must not return a value of a timestamp with a time zone. End of change

The expression can contain any of the following supported operands:

A constant
A special register
A variable
A built-in scalar function whose arguments are supported operands
A CAST specification where the cast operand is a supported operand
An expression that uses arithmetic operators and operands

Notes

Correlated references in table-reference:

In general, nested table expressions and table functions can be specified in any FROM clause. Columns from the nested table expressions and table functions can be referenced in the select list and in the rest of the fullselect using the correlation name. The scope of this correlation name is the same as correlation names for other table or view names in the FROM clause. The basic rule that applies for both these cases is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries.

Nested table expressions can be used in place of a view to avoid creating a view when general use of the view is not required. They can also be used when the result table is based on host variables.

For table functions, an additional capability exists. A table function can contain one or more correlated references to other tables in the same FROM clause if the referenced tables precede the reference in the left-to-right order of the tables in the FROM clause. The same capability exists for nested table expressions if the optional keyword TABLE is specified; otherwise, only references to higher levels in the hierarchy of subqueries is allowed.

A nested table expression or table function that contains correlated references to other tables in the same FROM clause:

Cannot participate in a FULL OUTER JOIN or a RIGHT OUTER JOIN
Can participate in LEFT OUTER JOIN or an INNER JOIN if the referenced tables precede the reference in the left-to-right order of the tables in the FROM clause

The following table shows some examples of valid and invalid correlated references. TABF1 and TABF2 represent table functions.

Table 1. Examples of correlated references
Subselect	Valid	Reason
`SELECT T.C1, Z.C5 FROM TABLE( TABF1(T.C2) ) AS Z, T WHERE T.C3 = Z.C4;`	No	`T.C2` cannot be resolved because `T` does not precede `TABF1` in FROM
`SELECT T.C1, Z.C5 FROM T, TABLE( TABF1(T.C2) ) AS Z WHERE T.C3 = Z.C4;`	Yes	`T` precedes `TABF1` in FROM, making `T.C2` known
`SELECT A.C1, B.C5 FROM TABLE( TABF2(B.C2) ) AS A, TABLE( TABF1(A.C6) ) AS B WHERE A.C3 = B.C4;`	No	`B` in `B.C2` cannot be resolved because the table function that would resolve it, `TABF1`, follows its reference in `TABF2` in FROM
`SELECT D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT FROM DEPT D, (SELECT AVG(E.SALARY) AS AVGSAL, COUNT(*) AS EMPCOUNT FROM EMP E WHERE E.WORKDEPT = D.DEPTNO) AS EMPINFO;`	No	`DEPT` precedes nested table expression, but keyword TABLE is not specified, making `D.DEPTNO` unknown
`SELECT D.DEPTNO, D.DEPTNAME, EMPINFO.AVGSAL, EMPINFO.EMPCOUNT FROM DEPT D, TABLE (SELECT AVG(E.SALARY) AS AVGSAL, COUNT(*) AS EMPCOUNT FROM EMP E WHERE E.WORKDEPT = D.DEPTNO) AS EMPINFO;`	Yes	`DEPT` precedes nested table expression and keyword TABLE is specified, making `D.DEPTNO` known