table-reference

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

table-reference:

Read syntax diagramSkip visual syntax diagramsingle-table-referencesingle-view-referencenested-table-expressiondata-change-table-referencetable-function-referencetable-locator-referencexmltable-expressioncollection-derived-tablejoined-table

single-table-reference:

Read syntax diagramSkip visual syntax diagramtable-nameperiod-specificationcorrelation-clause

single-view-reference:

Read syntax diagramSkip visual syntax diagramview-nameperiod-specificationcorrelation-clause

period-specification:

Read syntax diagramSkip visual syntax diagramFOR SYSTEM_TIME1BUSINESS_TIME2 AS OFvalueFROMvalue1TOvalue2BETWEENvalue1ANDvalue2
Notes:
  • 1 AS OF TIMESTAMP can be specified as an alternative and is treated as if FOR SYSTEM_TIME AS OF had been specified.
  • 2 SYSTEM_TIME and BUSINESS_TIME cannot be specified more than one time per table.
Start of change

nested-table-expression:

End of change
Read syntax diagramSkip visual syntax diagramTABLE( fullselect)correlation-clause

data-change-table-reference:

Read syntax diagramSkip visual syntax diagramFINAL TABLE( INSERT statement)FINALOLDTABLE( searched UPDATE statement)OLD TABLE( searched DELETE statement)FINAL TABLE( MERGE statement)correlation-clause

table-function-reference:

Read syntax diagramSkip visual syntax diagramTABLE( function-name(,expressionTABLEtransition-table-name)table-UDF-cardinality-clause)correlation-clausetyped-correlation-clause1
Notes:
  • 1 The typed-correlation-clause is required for generic table functions. This clause cannot be specified for any other table functions.

table-UDF-cardinality-clause:

Read syntax diagramSkip visual syntax diagramCARDINALITYinteger-constantCARDINALITY MULTIPLIERnumeric-constant

table-locator-reference:

Read syntax diagramSkip visual syntax diagramTABLE(table-locator-variable LIKEtable-name)correlation-name
Start of change

xmltable-expression:

End of change
Read syntax diagramSkip visual syntax diagramxmltable-functioncorrelation clause
A table-reference specifies an intermediate result table.
  • If a single-table-reference is specified and it is not an archive-enabled table or a temporal table, the intermediate result table is the specified table. If a period-specification is also specified, the intermediate result table consists of the rows of the temporal table where the period matches the specification.
  • If a single-table-reference is specified and it is an archive-enabled table, the setting of the SYSIBMADM.GET_ARCHIVE global variable and the ARCHIVESENSITIVE bind option determine the contents of the intermediate result table. If the global variable is set to Y and the bind option is set to YES, the intermediate result table includes the rows in the associated archive table. Otherwise, the intermediate result table does not include rows in the associated archive table.
  • If a single-view-reference is specified without a period-specification, the intermediate result table is that view. If a period-specification is specified, temporal table references in the view consider only the rows 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 a collection-derived-table is specified, the intermediate result table is a set of rows from one or more array values. For more information, see collection-derived-table.
  • If an xmltable-expression is specified, the intermediate result table is the set of rows that are returned by the XMLTABLE table function 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. Start of changetable-locator-reference must not be specified in the body of a trigger.End of change

Start of changetable-name must refer to an EBCDIC table with a Unicode column if the transition table that is identified by table-locator-variable is for a trigger that is defined on an EBCDIC table with a Unicode column.End of change

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.

expression must not contain a scalar fullselect, a function, or a reference to a column.

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.

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.

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 0–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. 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.

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. For more information, see select-clause. 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.

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 statement. 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 statement. The MERGE statement must conform to the following rules:
  • Start of changeThe target of the MERGE 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.End of change
  • Start of changeThe target table or view of the MERGE statement must not have a column with a ROWID data type. Additionally, when NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified, the target table or view of the MERGE statement must not have a column with a LOB or XML data type.End of change
  • Start of changeIf table-reference is specified in the MERGE statement, it must not contain correlated references to columns that are outside of the table reference in the MERGE statement.End of change
  • Start of changeIf table-reference is specified in the MERGE statement, AFTER triggers that result in further operations on the target table must not exist.End of change
  • Start of changeWhen NOT ATOMIC CONTINUE ON SQLEXCEPTION is specified in the MERGE statement, or the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause is not specified, and source-values (VALUES) is specified, the MERGE statement must not include a delete operation.End of change
searched UPDATE statement
Specifies a searched UPDATE statement as described in UPDATE statement. 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.

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

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 statement. 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.

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.

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. For more information, see correlation-clause.
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

Start of changeIf a correlation-clause clause does not follow an xmltable-expression reference, a nested-table-expression reference, or a collection-derived-table-reference, there is no exposed name for that table reference.End of change

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
  • Start of changeColumn names specified in the COLUMNS clause of the xmltable-expression when the table-reference is an xmltable-expressionEnd of change
  • Start of changeColumn names returned by the fullselect when the table-reference is a nested-table-expressionEnd of change
  • 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.
typed-correlation-clause
A typed-correlation-clause defines the appearance and contents of the table generated by a generic table function. This clause must be specified when the table-function-reference is a generic table function and cannot be specified for any other table reference. For more information, see typed-correlation-clause.
xmltable-expression
Specifies an invocation of the built-in XMLTABLE function. For more information, see XMLTABLE table function.

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.

collection-derived-table
A collection-derived-table is used to convert the elements of one or more arrays into column values in separate rows of an intermediate result table, as explained in collection-derived-table.
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. If the table reference specifies a view, the definition of that view must not reference a user-defined function.

The rows of the table reference are derived by application of the specified period specification. Start of changeThe intermediate result table does not include rows in the associated history table that were added for the ON DELETE ADD EXTRA ROW attribute in the system-period temporal table definition.End of change

Note: Start of changeHistory tables are intended to include only rows that Db2 stores to record the history of the associated system-period temporal table. However, if the history table contains other rows with the same value in the two columns that correspond to the row-begin and row-end columns in the system-period temporal table, the intermediate result table might include these rows. These rows might be included in the following cases:
  • The system-period temporal table is defined with the ON DELETE ADD EXTRA ROW attribute, the table contains a DATA CHANGE OPERATION column, and the value of the corresponding column in the history table is not 'D'.
  • The system-period temporal table is not defined with the ON DELETE ADD EXTRA ROW attribute.
End of change

The rows of a view reference are derived by application of the specified period specifications to all of the temporal tables that are accessed when computing the result table of the view. If the view does not access any temporal tables, the period specification has no effect on the result table of the view.

If the table is a bitemporal table and a period-specification is not specified for both 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.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a value other than the null value, a period-specification for a table or view cannot reference SYSTEM_TIME. This restriction applies even if the view body does not reference a system-period temporal table. The exception is if the value in effect for the SYSTIMESENSITIVE bind option is NO. In this case, the period-specification can reference SYSTEM_TIME.

If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a value other than the null value, a period-specification for a table or view cannot reference BUSINESS_TIME. This restriction applies even if the view body does not reference an application-period temporal table. The exception is if the value in effect for the BUSTIMESENSITIVE bind option is NO. In this case, the period-specification can reference BUSINESS_TIME.

For more information, see:

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

Do not specify FOR SYSTEM_TIME if the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not NULL and the SYSTIMESENSITIVE bind option is set to YES .

FOR BUSINESS_TIME
Specifies that the BUSINESS_TIME period is used for the period-specification. The table reference must be an application-period temporal table or a view.

Do not specify FOR BUSINESS_TIME if the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not NULL and the BUSTIMESENSITIVE bind option is set to YES .

AS OF value
Start of changeSpecifies that the table-reference includes rows that exist at the time that is specified by value as follows: End of changeStart of change
  • For an inclusive-exclusive period, a row is included if 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. If value is the null value, the table reference is an empty table.
  • For an inclusive-inclusive period, a row is included if the begin value for the specified period is less than or equal to value, and the end value for the period is greater than or equal to value. If value is the null value, the table reference is an empty table.
End of change
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.

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

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.

The expression can contain any of the following supported operands:
  • A constant
  • A special register
  • A variable
  • An array element specification
  • 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

A period specification for a view must not contain an untyped parameter marker.

FROM value1 TO value2
Specifies that the table-reference includes rows that exist for the period that is specified from value1 up to value2. Start of change
  • For an inclusive-exclusive period, a row is included in the 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-reference contains 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.
  • For an inclusive-inclusive period, a row is included in the 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 or equal to value1. The table-reference contains zero rows if value1 is greater than or equal tovalue2. If value1 or value2 is the null value, the table reference is an empty table.
End of change
value1 or value2
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.

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

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.

The expression can contain any of the following supported operands:
  • A constant
  • A special register
  • A variable
  • An array element specification
  • 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

A period specification for a view must not contain an untyped parameter marker.

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. Start of change
  • For an inclusive-exclusive period, 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.
  • For an inclusive-inclusive period, 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 or equal to 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.
End of change
value1 or value2
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.

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

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.

The expression can contain any of the following supported operands:
  • A constant
  • A special register
  • A variable
  • An array element specification
  • 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

A period specification for a view must not contain an untyped parameter marker.

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
Affects of special registers:
The setting of the CURRENT TEMPORAL BUSINESS_TIME and CURRENT TEMPORAL SYSTEM_TIME special registers might affect the result of a query, as described in the following situations:
  • Assume the following conditions:
    • A table reference is an application-period temporal table.
    • The columns of the BUSINESS_TIME period are defined as TIMESTAMP(6).
    • The CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value.
    In this case, a query is executed as if it contained the following specification: 
    FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME
  • Assume the following conditions:
    • A table reference is an application-period temporal table.
    • The columns of the BUSINESS_TIME period are defined as DATE.
    • The CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value.
    In this case, a query is executed as if it contained the following specification: 
    FOR BUSINESS_TIME AS OF CAST(CURRENT TEMPORAL BUSINESS_TIME AS DATE)
  • If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, a query is executed as if it contained the following specification: 
    FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME