table-reference

>>-+-| single-table-reference |------+-------------------------><
   +-| single-view-reference |-------+   
   +-| single-nickname-reference |---+   
   +-| only-table-reference |--------+   
   +-| outer-table-reference |-------+   
   +-| analyze_table-expression |----+   
   +-| nested-table-expression |-----+   
   +-| data-change-table-reference |-+   
   +-| table-function-reference |----+   
   +-| collection-derived-table |----+   
   +-| xmltable-expression |---------+   
   |                  (1)            |   
   '-| joined-table |----------------'   

single-table-reference

               .------------------------------.                               
               V                              |                               
|--table-name----+--------------------------+-+--+------------------------+-->
                 '-| period-specification |-'    '-| correlation-clause |-'   

>--+------------------------+-----------------------------------|
   '-| tablesample-clause |-'   

single-view-reference

              .------------------------------.                               
              V                              |                               
|--view-name----+--------------------------+-+--+------------------------+--|
                '-| period-specification |-'    '-| correlation-clause |-'   

single-nickname-reference

|--nickname--+------------------------+-------------------------|
             '-| correlation-clause |-'   

only-table-reference

|--ONLY--(--+-table-name-+--)--+------------------------+-------|
            '-view-name--'     '-| correlation-clause |-'   

outer-table-reference

|--OUTER--(--+-table-name-+--)--+------------------------+------|
             '-view-name--'     '-| correlation-clause |-'   

analyze_table-expression

|--+-table-name-+----------------------------------------------->
   '-view-name--'   

>--ANALYZE_TABLE--(--| implementation-clause |--)---------------|

implementation-clause

|--IMPLEMENTATION--'--string--'---------------------------------|

nested-table-expression

|--+-----------------------------------------------+--(fullselect)--+------------------------+--|
   |         (2)                                   |                '-| correlation-clause |-'   
   '-LATERAL------+------------------------------+-'                                             
                  '-| continue-handler |--WITHIN-'                                               

data-change-table-reference

|--+-+-FINAL-+--TABLE--(--insert-statement--)----------+--+------------------------+--|
   | '-NEW---'                                         |  '-| correlation-clause |-'   
   +-+-FINAL-+--TABLE--(--searched-update-statement--)-+                               
   | +-NEW---+                                         |                               
   | '-OLD---'                                         |                               
   '-OLD TABLE--(--searched-delete-statement--)--------'                               

table-function-reference

|--TABLE--(--function-name--(--+----------------+--)--)--+----------------------------------+--|
                               | .-,----------. |        +-| correlation-clause |-----------+   
                               | V            | |        |                              (3) |   
                               '---expression-+-'        '-| typed-correlation-clause |-----'   

collection-derived-table

|--UNNEST-table-function--+---------------------+--+------------------------+--|
                          |                 (4) |  '-| correlation-clause |-'   
                          '-WITH ORDINALITY-----'                               

xmltable-expression

                     (5)                               
|--xmltable-function------+------------------------+------------|
                          '-| correlation-clause |-'   

period-specification

|--FOR--+-SYSTEM_TIME---+--+-AS OF--value-----------------+-----|
        '-BUSINESS_TIME-'  +-FROM--value1--TO--value2-----+   
                           '-BETWEEN--value1--AND--value2-'   

correlation-clause

   .-AS-.                                                
|--+----+--correlation-name--+-----------------------+----------|
                             |    .-,-----------.    |   
                             |    V             |    |   
                             '-(----column-name-+--)-'   

tablesample-clause

|--TABLESAMPLE--+-BERNOULLI-+--(--numeric-expression1--)-------->
                '-SYSTEM----'                              

>--+---------------------------------------+--------------------|
   '-REPEATABLE--(--numeric-expression2--)-'   

typed-correlation-clause

   .-AS-.                                                           
|--+----+--correlation-name--+----------------------------------+--|
                             |    .-,----------------------.    |   
                             |    V                        |    |   
                             '-(----column-name--data-type-+--)-'   

continue-handler

                      .-,----------------------------.   
                      V                              |   
|--RETURN DATA UNTIL----| specific-condition-value |-+----------|

specific-condition-value

                        .-VALUE-.                                                       
|--FEDERATED--SQLSTATE--+-------+--string-constant--+-------------------------------+--|
                                                    |          .-,----------------. |   
                                                    |          V                  | |   
                                                    '-SQLCODE----integer-constant-+-'   

1. The syntax for joined-table is covered in a separate topic; refer to joined-table.
2. TABLE can be specified in place of LATERAL.
3. The typed-correlation-clause is required for generic table functions. This clause cannot be specified for any other table functions.
4. WITH ORDINALITY can be specified only if the argument to the UNNEST table function is one or more ordinary array variables or functions with ordinary array return types; an associative array variable or function with an associative array return type cannot be specified (SQLSTATE 428HT).
5. An XMLTABLE function can be part of a table-reference. In this case, subexpressions within the XMLTABLE expression are in-scope of prior range variables in the FROM clause. For more information, see the description of "XMLTABLE".

single-table-reference

Each table-name specified as a table-reference must identify an existing table at the application server or an existing table at a remote server specified using a remote-object-name. The intermediate result table is the result of the table. If the table-name references a typed table, the intermediate result table is the UNION ALL of the table with all its subtables, with only the columns of the table-name. A period-specification can be used with a temporal table to specify the period from which the rows are returned as the intermediate result table. A tablesample-clause can be used to specify that a sample of the rows be returned as the intermediate result table.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value CTST and table-name identifies a system-period temporal table, the table reference is executed as if it contained the following specification with the special register set to the null value:

   table-name FOR SYSTEM_TIME AS OF CTST

If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value CTBT and table-name identifies an application-period temporal table, the table reference is executed as if it contained the following specification with the special register set to the null value:

   table-name FOR BUSINESS_TIME AS OF CTBT

single-view-reference

Each view-name specified as a table-reference must identify one of the following objects:

• An existing view at the application server
• A view at a remote server specified using a remote-object-name
• The table-name of a common table expression

The intermediate result table is the result of the view or common table expression. If the view-name references a typed view, the intermediate result table is the UNION ALL of the view with all its subviews, with only the columns of the view-name. A period-specification can be used with a view defined over a temporal table to specify the period from which the rows are returned as the intermediate result table.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value CTST, and view-name identifies a system-period temporal table, the table reference is executed as if it contained the following specification with the special register set to the null value:

• view-name FOR SYSTEM_TIME AS OF CTST

If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value CTBT, and view-name identifies an application-period temporal table, the table reference is executed as if it contained the following specification with the special register set to the null value:

• view-name FOR BUSINESS_TIME AS OF CTBT

single-nickname-reference

Each nickname specified as a table-reference must identify an existing nickname at the application server. The intermediate result table is the result of the nickname.

only-table-reference

The use of ONLY(table-name) or ONLY(view-name) means that the rows of the applicable subtables or subviews are not included in the intermediate result table. If the table-name used with ONLY does not have subtables, then ONLY(table-name) is equivalent to specifying table-name. If the view-name used with ONLY does not have subviews, then ONLY(view-name) is equivalent to specifying view-name.

The use of ONLY requires the SELECT privilege on every subtable of table-name or subview of view-name.

outer-table-reference

The use of OUTER(table-name) or OUTER(view-name) represents a virtual table. If the table-name or view-name used with OUTER does not have subtables or subviews, then specifying OUTER is equivalent to not specifying OUTER. If the table-name does have subtables, the intermediate result table from OUTER(table-name) is derived from table-name as follows:

• The columns include the columns of table-name followed by the additional columns introduced by each of its subtables, if any. The additional columns are added on the right, traversing the subtable hierarchy in depth-first order. Subtables that have a common parent are traversed in creation order of their types.
• The rows include all the rows of table-name and all the rows of its subtables. Null values are returned for columns that are not in the subtable for the row.

If the view-name does have subviews, the intermediate result table from OUTER(view-name) is derived from view-name as follows:

• The columns include the columns of view-name followed by the additional columns introduced by each of its subviews, if any. The additional columns are added on the right, traversing the subview hierarchy in depth-first order. Subviews that have a common parent are traversed in creation order of their types.
• The rows include all the rows of view-name and all the rows of its subviews. Null values are returned for columns that are not in the subview for the row.

The use of OUTER requires the SELECT privilege on every subtable of table-name or subview of view-name.

analyze_table-expression

table-name | view-name

The table-name or view-name variable must identify an existing table or view or identify the table-name of a common table expression that you define preceding the fullselect containing the table-reference. You can specify a nickname. However, in-database analytics are intended for local data, and retrieving the data for a nickname from another data source does not take advantage of the intended performance benefits.

ANALYZE_TABLE

Returns the result of executing a specific data mining model by using an in-database analytics provider, a named model implementation, and input data. A query referencing the ANALYZE_TABLE parameter cannot be a static SQL statement or a data definition language (DDL) statement. Input or output values cannot be of type CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BLOB, CLOB, DBCLOB, NCLOB, XML, or DB2SECURITYLABEL.

IMPLEMENTATION ’string’

Specifies how the expression is to be evaluated. The string parameter is a string constant whose maximum length is 1024 bytes. The specified value is used to establish a session with an in-database analytic provider. When you specify SAS as the provider, you must specify values for the following case-insensitive parameters:

PROVIDER

Currently, the only supported provider value is SAS.

ROUTINE_SOURCE_TABLE

Specifies a user table containing the DS2 code (and, optionally, any required format or metadata) to implement the algorithm that is specified by the ROUTINE_SOURCE_NAME parameter. DS2 is a procedural language processor for SAS, designed for data modeling, stored procedures, and data extraction, transformation, and load (ETL) processing.

The routine source table has a defined structure (see the examples at the end of the "analyze_table-expression" section) and, in a partitioned database environment, must be on the catalog database partition. The table cannot be a global temporary table. The MODELDS2 column for a particular row must not be empty or contain the null value. If the value of the MODELFORMATS or MODELMETADATA column is not null, the value must have a length greater than 0. If you do not specify a table schema name, the value of the CURRENT SCHEMA special register is used.

ROUTINE_SOURCE_NAME

Specifies the name of the algorithm to use.

For example:

IMPLEMENTATION
  'PROVIDER=SAS;
  ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
  ROUTINE_SOURCE_NAME=SCORING_FUN1;'

If the table name, schema name, or algorithm name contains lowercase or mixed-case letters, specify delimited identifiers, as shown in the following example:

IMPLEMENTATION
  'PROVIDER=SAS;
  ROUTINE_SOURCE_TABLE="ETLin"."Source_Table";
  ROUTINE_SOURCE_NAME="Scoring_Fun1";'

The following examples show you how to use the ANALYZE_TABLE expression.

SAS tooling helps you to define a table to store model implementations for scoring functions. A row in this table stores an algorithm that is written in DS2, with any required SAS format information and metadata. The MODELNAME column serves as the primary key. For a particular value of the ROUTINE_SOURCE_NAME parameter, at most one row is retrieved from the table that the ROUTINE_SOURCE_TABLE parameter specifies. For example:

   CREATE TABLE ETLIN.SOURCE_TABLE (
     MODELNAME VARCHAR(128) NOT NULL PRIMARY KEY,
     MODELDS2 BLOB(4M) NOT NULL,
     MODELFORMATS BLOB(4M),
     MODELMETADATA BLOB(4M)
   );

The MODELNAME column contains the name of the algorithm. The MODELDS2 column contains the DS2 source code that implements the algorithm. The MODELFORMATS column contains the aggregated SAS format definition that the algorithm requires. If the algorithm does not require a SAS format, this column contains the null value. The MODELMETADATA column contains any additional metadata that the algorithm requires. If the algorithm does not require any additional metadata, this column contains the null value. If the SAS EP installer creates the table, it might include additional columns.

• Use the data in columns C1 and C2 in table T1 as input data with the scoring model SCORING_FUN1, whose implementation is stored in ETLIN.SOURCE_TABLE:

     WITH sas_score_in (c1,c2) AS
       (SELECT c1,c2 FROM t1)
       SELECT *
         FROM sas_score_in ANALYZE_TABLE(
           IMPLEMENTATION
             'PROVIDER=SAS;
              ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
              ROUTINE_SOURCE_NAME=SCORING_FUN1;');

• Use all the data in the table T2 with the scoring model SCORING_FUN2, whose implementation is stored in ETLIN.SOURCE_TABLE:

     SELECT *
       FROM t2 ANALYZE_TABLE(
         IMPLEMENTATION
           'PROVIDER=SAS;
            ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
            ROUTINE_SOURCE_NAME=SCORING_FUN2;');

• Use all the data in view V1 with the scoring model SCORING_FUN3, whose implementation is stored in ETLIN.SOURCE_TABLE, and return the output in ascending order of the first output column:

     SELECT *
       FROM v1 ANALYZE_TABLE(
         IMPLEMENTATION
           'PROVIDER=SAS;
            ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
            ROUTINE_SOURCE_NAME=SCORING_FUN3;')
       ORDER BY 1;

nested-table-expression

A fullselect in parentheses is called a nested table expression. The intermediate result table is the result of that fullselect. The columns of the result do not need unique names, but a column with a non-unique name cannot be explicitly referenced. If LATERAL is specified, the fullselect can include correlated references to results columns of table references specified to the left of the nested table expression. If the nested table expression involves data from a federated data source, a continue-handler can be specified to tolerate certain error conditions from the data source.

An expression in the select list of a nested table expression that is referenced within, or is the target of, a data change statement within a fullselect is valid only when it does not include:

• A function that reads or modifies SQL data
• A function that is non-deterministic
• A function that has external action
• An OLAP function

If a view is referenced directly in, or as the target of a nested table expression in a data change statement within a FROM clause, the view must meet either of the following conditions:

• Be symmetric (have WITH CHECK OPTION specified)
• Satisfy the restriction for a WITH CHECK OPTION view

If the target of a data change statement within a FROM clause is a nested table expression, the following restrictions apply:

• Modified rows are not requalified
• WHERE clause predicates are not reevaluated
• ORDER BY or FETCH FIRST operations are not redone

A nested 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)
• When the required intermediate result table is based on host variables

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 searched UPDATE, searched DELETE, or INSERT statement that is included in the clause. A data-change-table-reference can be specified as the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement, a SELECT INTO statement, or a common table expression. A data-change-table-reference can be specified as the only table reference in the only fullselect in a SET Variable statement (SQLSTATE 428FL). The target table or view of the data change statement is considered to be a table or view that is referenced in the query; therefore, the authorization ID of the query must have SELECT privilege on that target table or view. A data-change-table-reference clause cannot be specified in a view definition, materialized query table definition, or FOR statement (SQLSTATE 428FL).

The target of the UPDATE, DELETE, or INSERT statement cannot be a temporary view defined in a common table expression (SQLSTATE 42807) or a nickname (SQLSTATE 25000).

Expressions in the select list of a view or fullselect as target of a data change statement in a table-reference can be selected only if OLD TABLE is specified or the expression does not include the following elements (SQLSTATE 428G6):

• A subquery
• A function that reads or modifies SQL data
• A function is that is non-deterministic or has an external action
• An OLAP function
• A NEXT VALUE FOR sequence reference

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 exist at the completion of the data change statement. If there are AFTER triggers or referential constraints that result in further operations on the table that is the target of the SQL data change statement, an error is returned (SQLSTATE 560C6). 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 (SQLSTATE 428G3).

NEW TABLE

Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement before the application of referential constraints and AFTER triggers. Data in the target table at the completion of the statement might not match the data in the intermediate result table because of additional processing for referential constraints and AFTER triggers.

OLD 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 existed before the application of the data change statement.

(searched-update-statement)

Specifies a searched UPDATE statement. A WHERE clause or a SET clause in the UPDATE statement cannot contain correlated references to columns outside of the UPDATE statement.

(searched-delete-statement)

Specifies a searched DELETE statement. A WHERE clause in the DELETE statement cannot contain correlated references to columns outside of the DELETE statement.

(insert-statement)

Specifies an INSERT statement. A fullselect in the INSERT statement cannot contain correlated references to columns outside of the fullselect of the INSERT statement.

The content of the intermediate result table for a data-change-table-reference is determined when the cursor opens. The intermediate result table contains all manipulated rows, including all the columns in the specified target table or view. All the columns of the target table or view for an SQL data change statement are accessible using the column names from the target table or view. If an INCLUDE clause was specified within a data change statement, the intermediate result table will contain these additional columns.

table-function-reference

In general, a table function, together with its argument values, can be referenced in the FROM clause of a SELECT in exactly the same way as a table or view. Each function-name together with the types of its arguments, specified as a table reference must resolve to an existing table function at the application server. There are, however, some special considerations which apply.

• Table function column names: Unless alternative column names are provided following the correlation-name, the column names for the table function are those specified in the RETURNS or RETURNS GENERIC TABLE clause of the CREATE FUNCTION statement. This is analogous to the names of the columns of a table, which are defined in the CREATE TABLE statement.
• Table function resolution: The arguments specified in a table function reference, together with the function name, are used by an algorithm called function resolution to determine the exact function to be used. This is no different from what happens with other functions (such as scalar functions) that are used in a statement.
• Table function arguments: As with scalar function arguments, table function arguments can generally be any valid SQL expression. The following examples are valid syntax:

    Example 1:  SELECT c1
                FROM TABLE( tf1('Zachary') ) AS z
                WHERE c2 = 'FLORIDA';
  
    Example 2:  SELECT c1
                FROM TABLE( tf2 (:hostvar1, CURRENT DATE) ) AS z;
  
    Example 3:  SELECT c1
                FROM t
                WHERE c2 IN
                         (SELECT c3 FROM
                          TABLE( tf5(t.c4) ) AS z  -- correlated reference
                         )                         -- to previous FROM clause

    Example 4:  SELECT c1
                FROM TABLE( tf6('abcd') )                -- tf6 is a generic
                    AS z (c1 int, c2 varchar(100)) -- java table function

• Table functions that modify SQL data: Table functions that are specified with the MODIFIES SQL DATA option can be used only as the last table reference in a select-statement, common-table-expression, or RETURN statement that is a subselect, a SELECT INTO, or a row-fullselect in a SET statement. Only one table function is allowed in one FROM clause, and the table function arguments must be correlated to all other table references in the subselect (SQLSTATE 429BL). The following examples have valid syntax for a table function with the MODIFIES SQL DATA property:

    Example 1:  SELECT c1
                FROM TABLE( tfmod('Jones') ) AS z
  
    Example 2:  SELECT c1
                FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) ) AS z
  
    Example 3:  SET var =
                (SELECT c1
                FROM TABLE( tfmod('Jones') ) AS z
  
    Example 4:  RETURN SELECT c1
                FROM TABLE( tfmod('Jones') ) AS z
  
    Example 5:  WITH v1(c1) AS
                (SELECT c1
                FROM TABLE( tfmod(:hostvar1) ) AS z)
                SELECT c1 
                FROM v1, t1 WHERE v1.c1 = t1.c1

    Example 6: SELECT z.* 
               FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) ) 
               AS z (col1 int)

collection-derived-table

A collection-derived-table can be used to convert the elements of an array into values of a column in separate rows. If WITH ORDINALITY is specified, an extra column of data type INTEGER is appended. This column contains the position of the element in the array. The columns can be referenced in the select list and the in rest of the subselect by using the names specified for the columns in the correlation-clause. The collection-derived-table clause can be used only in a context where arrays are supported (SQLSTATE 42887). See the “UNNEST table function” for details.

xmltable-expression

An xmltable-expression specifies an invocation of the built-in XMLTABLE function which determines the intermediate result table. See XMLTABLE for more information.

joined-table

A joined-table specifies an intermediate result set that is the result of one or more join operations. For more information, see joined-table.

period-specification

A period-specification identifies an intermediate result table consisting of the rows of the referenced table where the period matches the specification. A period-specification can be specified following the name of a temporal table or the name of a view. The same period name must not be specified more than once for the same table reference (SQLSTATE 428HY). The rows of the table reference are derived by application of the period specifications.

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 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 both SYSTEM_TIME and BUSINESS_TIME, the table reference includes all current rows of the table and does not include any historical rows of the table.

If the table reference is a single-view-reference, the rows of the view reference are derived by application of the period specifications to all of the temporal tables accessed when computing the result table of the view. If the view does not access any temporal table, then the period-specification has no effect on the result table of the view. If period-specification is used, the view definition or any view definitions referenced when computing the result table of the view must not include any references to compiled SQL functions or external functions with a data access indication other than NO SQL (SQLSTATE 428HY).

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a value other than the null value, then a period-specification that references SYSTEM_TIME must not be specified for the table reference or view reference, unless the value in effect for the SYSTIMESENSITIVE bind option is NO (SQLSTATE 428HY).

If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a value other than the null value, then a period specification that references BUSINESS_TIME must not be specified for the table reference or view reference, unless the value in effect for the BUSTIMESENSITIVE bind option is NO (SQLSTATE 428HY).

FOR SYSTEM_TIME

Specifies that the SYSTEM_TIME period is used for the period-specification. If the clause is specified following a table-name, the table must be a system-period temporal table (SQLSTATE 428HY). FOR SYSTEM_TIME must not be specified if the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not the null value and the SYSTIMESENSITIVE bind option is set to YES (SQLSTATE 428HY).

FOR BUSINESS_TIME

Specifies that the BUSINESS_TIME period is used for the period-specification. If the clause is specified following a table-name, BUSINESS_TIME must be a period defined in the table (SQLSTATE 4274M). FOR BUSINESS_TIME must not be specified if the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value and the BUSTIMESENSITIVE bind option is set to YES (SQLSTATE 428HY).

value, value1, and value2

The value, value1, and value2 expressions return the null value or a value of one of the following built-in data types (SQLSTATE 428HY): a DATE, a TIMESTAMP, or a character string that is not a CLOB or DBCLOB. If the argument is a character string, it must be a valid character string representation of a timestamp or a date (SQLSTATE 22007). For the valid formats of string representations of timestamp values, see the section "String representations of datetime values" in the topic "Datetime values".

Each expression can contain any of the following supported operands (SQLSTATE 428HY):

• Constant
• Special register
• Variable (host-variable, SQL parameter, SQL variable, transition variable)
• Parameter marker
• Scalar function whose arguments are supported operands (user-defined functions and non-deterministic functions cannot be used)
• CAST specification where the cast operand is a supported operand
• Expression using arithmetic operators and operands

AS OF value

Specifies that the table reference includes each row for which the value of the begin column for the specified period is less than or equal to value, and the value of the end column for the period is greater than value. If value is the null value, the table reference is an empty table.

Example: The following query returns the insurance coverage information for insurance policy number 100 on August 31, 2010.

SELECT coverage FROM policy_info FOR BUSINESS_TIME
		AS OF '2010-08-31' WHERE policy_id = '100'

FROM value1 TO value2

Specifies that the table reference includes rows that exist for the period specified from value1 to value2. A row is included in the table reference if the value of the begin column for the specified period in the row is less than value2, and the value of the end column for the specified 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.

Example: The following query returns the insurance coverage information for insurance policy 100, during the year 2009 (from January 1, 2009 at 12:00 AM until before January 1, 2010).

SELECT coverage FROM policy_info FOR BUSINESS_TIME
		FROM '2009-01-01' TO '2010-01-01' WHERE policy_id = '100'

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 value of the begin column for the specified period in the row is less than or equal to value2 and the value of the end column for the specified period in the row is greater than value1. The table reference contains zero rows if value1 is greater than value2. If value1 is equal to value2, the expression is equivalent to AS OF value1. If value1 or value2 is the null value, the table reference is an empty table.

Example: The following query returns the insurance coverage information for insurance policy number 100, during the year 2008 (between January 1, 2008 and December 31, 2008 inclusive).

SELECT coverage FROM policy_info FOR BUSINESS_TIME 
		BETWEEN '2008-01-01' AND '2008-12-31' WHERE policy_id = '100'

Following are syntax alternatives for period-specification clauses:

• AS OF TIMESTAMP can be specified in place of FOR SYSTEM_TIME AS OF
• VERSIONS BETWEEN TIMESTAMP can be specified in place of FOR SYSTEM_TIME BETWEEN

correlation-clause

The exposed names of all table references must be unique. An exposed name is:

• 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 nickname that is not followed by a correlation-name
• An alias-name that is not followed by a correlation-name

If a correlation-clause clause does not follow a function-name reference, xmltable-expression expression, nested table expression, or data-change-table-reference reference, or if a typed-correlation-clause clause does not follow a function-name reference, then there is no exposed name for that table reference.

Each correlation-name is defined as a designator of the immediately preceding table-name, view-name, nickname, function-name reference, xmltable-expression, nested table expression, or data-change-table-reference. Any qualified reference to a column must use the exposed name. If the same table name, view, or nickname 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, view or nickname. 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, view, or nickname when the table-reference is a table-name, view-name, nickname, or alias-name
• Column names specified in the RETURNS clause of the CREATE FUNCTION statement when the table-reference is a function-name reference
• Column names specified in the COLUMNS clause of the xmltable-expression when the table-reference is an xmltable-expression
• Column names exposed by the fullselect when the table-reference is a nested-table-expression
• 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

typed-correlation-clause

A typed-correlation-clause clause defines the appearance and contents of the table generated by a generic table function. This clause must be specified when the table-function-references is a generic table function and cannot be specified for any other table reference. The following data-type values are supported in generic table functions:

Table 1. Data types supported in generic table functions

SQL column data type	Equivalent Java™ data type
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
DOUBLE	double
DECIMAL(p,s)	java.math.BigDecimal
NUMERIC(p,s)	java.math.BigDecimal
CHAR(n)	java.lang.String
CHAR(n) FOR BIT DATA	COM.ibm.db2.app.Blob
VARCHAR(n)	java.lang.String
VARCHAR(n) FOR BIT DATA	COM.ibm.db2.app.Blob
GRAPHIC(n)	java.lang.String
VARGRAPHIC(n)	String
BLOB(n)	COM.ibm.db2.app.Blob
CLOB(n)	COM.ibm.db2.app.Clob
DBCLOB(n)	COM.ibm.db2.app.Clob
DATE	String
TIME	String
TIMESTAMP	String
XML AS CLOB(n)	COM.ibm.db2.jcc.DB2Xml

tablesample-clause

The optional tablesample-clause can be used to obtain a random subset (a sample) of the rows from the specified table-name, rather than the entire contents of that table-name, for this query. This sampling is in addition to any predicates that are specified in the where-clause. Unless the optional REPEATABLE clause is specified, each execution of the query will usually yield a different sample, except in degenerate cases where the table is so small relative to the sample size that any sample must return the same rows. The size of the sample is controlled by the numeric-expression1 in parentheses, representing an approximate percentage (P) of the table to be returned.

TABLESAMPLE

The method by which the sample is obtained is specified after the TABLESAMPLE keyword, and can be either BERNOULLI or SYSTEM. For both methods, the exact number of rows in the sample might be different for each execution of the query, but on average is approximately P percent of the table, before any predicates further reduce the number of rows.

The table-name must be a stored table. It can be a materialized query table (MQT) name, but not a subselect or table expression for which an MQT has been defined, because there is no guarantee that the database manager will route to the MQT for that subselect.

Semantically, sampling of a table occurs before any other query processing, such as applying predicates or performing joins. Repeated accesses of a sampled table within a single execution of a query (such as in a nested-loop join or a correlated subquery) will return the same sample. More than one table can be sampled in a query.

BERNOULLI

BERNOULLI sampling considers each row individually. It includes each row in the sample with probability P/100 (where P is the value of numeric-expression1), and excludes each row with probability 1 - P/100, independently of the other rows. So if the numeric-expression1 evaluated to the value 10, representing a ten percent sample, each row would be included with probability 0.1, and excluded with probability 0.9.

SYSTEM

SYSTEM sampling permits the database manager to determine the most efficient manner in which to perform the sampling. In most cases, SYSTEM sampling applied to a table-name means that each page of table-name is included in the sample with probability P/100, and excluded with probability 1 - P/100. All rows on each page that is included qualify for the sample. SYSTEM sampling of a table-name generally executes much faster than BERNOULLI sampling, because fewer data pages are retrieved. However, SYSTEM sampling can often yield less accurate estimates for aggregate functions, such as SUM(SALES), especially if the rows of table-name are clustered on any columns referenced in that query. The optimizer might in certain circumstances decide that it is more efficient to perform SYSTEM sampling as if it were BERNOULLI sampling. An example is when a predicate on table-name can be applied by an index and is much more selective than the sampling rate P.

numeric-expression1

The numeric-expression1 specifies the size of the sample to be obtained from table-name, expressed as a percentage. It must be a constant numeric expression that cannot contain columns. The expression must evaluate to a positive number that is less than or equal to 100, but can be between 1 and 0. For example, a value of 0.01 represents one one-hundredth of a percent, meaning that 1 row in 10 000 is sampled, on average. A numeric-expression1 that evaluates to 100 is handled as if the tablesample-clause were not specified. If numeric-expression1 evaluates to the null value, or to a value that is greater than 100 or less than 0, an error is returned (SQLSTATE 2202H).

REPEATABLE (numeric-expression2)

It is sometimes desirable for sampling to be repeatable from one execution of the query to the next; for example, during regression testing or query debugging. This can be accomplished by specifying the REPEATABLE clause. The REPEATABLE clause requires the specification of a numeric-expression2 in parentheses, which serves the same role as the seed in a random number generator. Adding the REPEATABLE clause to the tablesample-clause of any table-name ensures that repeated executions of that query (using the same value for numeric-expression2) return the same sample, assuming that the data itself has not been updated, reorganized, or repartitioned. To guarantee that the same sample of table-name is used across multiple queries, use of a global temporary table is recommended. Alternatively, the multiple queries can be combined into one query, with multiple references to a sample that is defined using the WITH clause.

• Example 1: Request a 10% Bernoulli sample of the Sales table for auditing purposes.

    SELECT * FROM Sales
      TABLESAMPLE BERNOULLI(10)

• Example 2: Compute the total sales revenue in the Northeast region for each product category, using a random 1% SYSTEM sample of the Sales table. The semantics of SUM are for the sample itself, so to extrapolate the sales to the entire Sales table, the query must divide that SUM by the sampling rate (0.01).

  SELECT SUM(Sales.Revenue) / (0.01)
    FROM Sales TABLESAMPLE SYSTEM(1)
    WHERE Sales.RegionName = 'Northeast'
    GROUP BY Sales.ProductCategory

• Example 3: Using the REPEATABLE clause, modify the previous query to ensure that the same (yet random) result is obtained each time the query is executed. The value of the constant enclosed by parentheses is arbitrary.

  SELECT SUM(Sales.Revenue) / (0.01)
    FROM Sales TABLESAMPLE SYSTEM(1) REPEATABLE(3578231)
    WHERE Sales.RegionName = 'Northeast'
    GROUP BY Sales.ProductCategory

continue-handler

Certain errors that occur within a nested-table-expression can be tolerated, and instead of returning an error, the query can continue and return a result. This is referred to as an error tolerant nested-table-expression.

Specifying the RETURN DATA UNTIL clause will cause any rows that are returned from the fullselect before the indicated condition is encountered to make up the result set from the fullselect. This means that a partial result set (which can also be an empty result set) from the fullselect is acceptable as the result for the nested-table-expression.

The FEDERATED keyword restricts the condition to handle only errors that occur at a remote data source.

The condition can be specified as an SQLSTATE value, with a string-constant length of 5. You can optionally specify an SQLCODE value for each specified SQLSTATE value. For portable applications, specify SQLSTATE values as much as possible, because SQLCODE values are generally not portable across platforms and are not part of the SQL standard.

Only certain conditions can be tolerated. Errors that do not allow the rest of the query to be executed cannot be tolerated, and an error is returned for the whole query. The specific-condition-value might specify conditions that cannot actually be tolerated by the database manager, even if a specific SQLSTATE or SQLCODE value is specified, and for these cases, an error is returned.

A query or view containing an error tolerant nested-table-expression is read-only.

The fullselect of an error tolerant nested-table-expression is not optimized using materialized query tables.

specific-condition-value

The following SQLSTATE values and SQLCODE values have the potential, when specified, to be tolerated by the database manager:

• SQLSTATE 08001; SQLCODEs -1336, -30080, -30081, -30082
• SQLSTATE 08004
• SQLSTATE 42501
• SQLSTATE 42704; SQLCODE -204
• SQLSTATE 42720
• SQLSTATE 28000

Correlated references in table-references

  Example 1:  SELECT t.c1, z.c5
              FROM t, TABLE( tf3(t.c2) ) AS z      -- t precedes tf3
              WHERE t.c3 = z.c4;                   -- in FROM, so t.c2
                                                   -- is known

  Example 2:  SELECT t.c1, z.c5
              FROM t, TABLE( tf4(2 * t.c2) ) AS z  -- t precedes tf4
              WHERE t.c3 = z.c4;                   -- in FROM, so t.c2
                                                   -- is known

  Example 3:  SELECT d.deptno, d.deptname,
                     empinfo.avgsal, empinfo.empcount
              FROM department d,
                   LATERAL (SELECT AVG(e.salary) AS avgsal,
                              COUNT(*) AS empcount
                       FROM employee e           -- department precedes nested
                       WHERE e.workdept=d.deptno -- table expression and
                      ) AS empinfo;              -- LATERAL is specified, 
                                                 -- so d.deptno is known

  Example 4:  SELECT t.c1, z.c5
              FROM TABLE( tf6(t.c2) ) AS z, t  -- cannot resolve t in t.c2!
              WHERE t.c3 = z.c4;               -- compare to Example 1 above.

  Example 5:  SELECT a.c1, b.c5
              FROM TABLE( tf7a(b.c2) ) AS a, TABLE( tf7b(a.c6) ) AS b
              WHERE a.c3 = b.c4;               -- cannot resolve b in b.c2!

  Example 6:  SELECT d.deptno, d.deptname,
                     empinfo.avgsal, empinfo.empcount
              FROM department d,
                   (SELECT AVG(e.salary) AS avgsal,
                              COUNT(*) AS empcount
                       FROM employee e           -- department precedes nested
                       WHERE e.workdept=d.deptno -- table expression but 
                      ) AS empinfo;              -- LATERAL is not specified,
                                                 -- so d.deptno is unknown