DB2 10.5 for Linux, UNIX, and Windows

VALUES INTO statement

The VALUES INTO statement produces a result table consisting of at most one row, and assigns the values in that row to host variables.

Invocation

This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include any privileges that are necessary to execute each expression and row-expression.

For each global variable used as an assignment-target, the privileges held by the authorization ID of the statement must include one of the following authorities:

WRITE privilege on the global variable that is not defined in a module
EXECUTE privilege on the module of the global variable that is defined in a module

Syntax


>>-VALUES--+-expression-----------+--INTO----------------------->
           |    .-,----------.    |         
           |    V            |    |         
           +-(----expression-+--)-+         
           '-row-expression-------'         

   .-,-------------------------.   
   V                           |   
>------| assignment-target |---+-------------------------------><


assignment-target

>>-+-global-variable-name-------------------+------------------><
   +-host-variable-name---------------------+   
   +-SQL-parameter-name---------------------+   
   +-SQL-variable-name----------------------+   
   +-transition-variable-name---------------+   
   +-array-variable-name--[--array-index--]-+   
   '-field-reference------------------------'

Description

VALUES

Introduces a single row consisting of one or more columns.

expression: An expression that defines a single value of a one column result table.
(expression,...): One or more expressions that define the values for one or more columns of the result table.
row-expression: Specifies the new row of values. The row-expression is any row expression of the type described in "Row expressions". The row-expression must not include a column name.

INTO assignment-target

Identifies one or more targets for the assignment of output values.

The first value in the result row is assigned to the first target in the list, the second value to the second target, and so on. Each assignment to an assignment-target is made in sequence through the list. If an error occurs on any assignment, no value is assigned to any assignment-target.

When the data type of every assignment-target is not a row type, then the value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of assignment-targets is less than the number of result column values.

If the data type of an assignment-target is a row type, then there must be exactly one assignment-target specified (SQLSTATE 428HR), the number of columns must match the number of fields in the row type, and the data types of the columns of the fetched row must be assignable to the corresponding fields of the row type (SQLSTATE 42821).

If the data type of an assignment-target is an array element, then there must be exactly one assignment-target specified.

global-variable-name

Identifies the global variable that is the assignment target.

host-variable-name

Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.

SQL-parameter-name

Identifies the name parameter that is the assignment target.

SQL-variable-name

Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.

transition-variable-name

Identifies the column to be updated in the transition row. A transition-variable-name must identify a column in the subject table of a trigger, optionally qualified by a correlation name that identifies the new value.

array-variable-name

Identifies an SQL variable, SQL parameter, or global variable of an array type.

[array-index]: An expression that specifies which element in the array will be the target of the assignment. For an ordinary array, the array-index expression must be assignable to INTEGER (SQLSTATE 428H1) and cannot be the null value. Its value must be between 1 and the maximum cardinality defined for the array (SQLSTATE 2202E). For an associative array, the array-index expression must be assignable to the index data type of the associative array (SQLSTATE 428H1) and cannot be the null value.

field-reference

Identifies the field within a row type value that is the assignment target. The field-reference must be specified as a qualified field-name where the qualifier identifies the row value in which the field is defined.

Rules

Global variables cannot be assigned inside triggers that are not defined using a compound SQL (compiled) statement, functions that are not defined using a compound SQL (compiled) statement, methods, or compound SQL (inlined) statements (SQLSTATE 428GX).

Examples

Example 1: This C example retrieves the value of the CURRENT PATH special register into a host variable.
```
   EXEC SQL VALUES(CURRENT PATH)
            INTO :hvl;
```
Example 2: This C example retrieves a portion of a LOB field into a host variable, exploiting the LOB locator for deferred retrieval.
```
   EXEC SQL VALUES (substr(:locator1,35))
            INTO :details;
```
Example 3: This C example retrieves the value of the SESSION_USER special register into a global variable.
```
   EXEC SQL VALUES(SESSION_USER)
     INTO GV_SESS_USER;
```