VALUES INTO
The
VALUES INTO statement assigns one or more values to variables.
Invocation
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization
The
privileges that are held by the authorization ID of the statement
must include at least one of the following privileges or authorities:- The SELECT privilege on every table and view identified in the statement
- Ownership of every table and view identified in the statement
- READ and WRITE privileges on any global variables that are identified in the statement
- Ownership of any global variables that are identified in the statement
- DBADM authority for the database (tables only)
- DATAACCESS authority
- SYSADM authority
- SYSCTRL authority (catalog tables only)

For an assignment to a global
variable, the privilege set must include at least one of the following:

- The WRITE privilege on the variable
- Ownership of the variable
- DATAACCESS authority
- SYSADM authority
Privilege set: The privilege set is the privileges that are held by the owner of the package.
Authorization is required for any expressions that are used in the statement. For more information, see Expressions.
Syntax
- 1 When the target is not an SQL parameter or an SQL variable that is used in a native SQL procedure or compiled SQL function, these special registers can be referenced only as a source value in this form of the syntax for this statement.
- 2 If expression is an array-constructor, it must not be the only source value specification in the list.
- 3 The number of source value specifications (expression, NULL, or DEFAULT) on the right side of the equal sign must match the number of target specifications on the left side of the statement.
Description
- VALUES
- Introduces a single row that
consists of one or more columns. If more than one value is specified,
the list of values must be enclosed within parentheses.
- expression
- The expression is any expression of the type described in Expressions. In a native SQL routine, the expression can contain a reference to the CURRENT PACKAGESET, CURRENT PACKAGE PATH, or CURRENT SERVER special registers if target-variable is an SQL parameter or SQL variable. The expression must not include a column name.
- NULL
- The null value. NULL can only be specified for host variables that have an associated indicator variable.
INTO target-variable or array-variable[array-index]
Identifies
one or more targets for the assignment of output values. The number
of targets in the INTO clause must equal the number of values that
are to be assigned. 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. A target variable must not be specified more than once
in the INTO clause. Each assignment to a target is made in sequence
through the list, according to the rules described in Assignment and comparison. The value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of targets is less than the number of result column values.
If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to the specified targets. Any values that have already been assigned remain assigned.
- 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 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.
- array-variable [array-index]
- Specifies an array element that is the target of the assignment.
An array element must not be specified as the target for an assignment if common-table-expression is also specified in the statement.
- array-variable
- Specifies an array variable.
- [array-index]
- An expression that specifies which element in the array is the
target of the assignment.
For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.
For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.
array-index must not be:
- An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
- A nondeterministic function
- A function that is defined with EXTERNAL ACTION
- A function that is defined with MODIFIES SQL DATA
- A sequence expression

Notes
Assignment to targets:

The nth target identified by the INTO clause corresponds to the nth column of the result table of the cursor. The data type of the target must be compatible with its corresponding value. If the value is numeric, the target must have the capacity to represent the whole part of the value. For a datetime value, the target must be a character string variable of a minimum length as defined in String representations of datetime values. When the value that is to be assigned is null, an indicator variable must be specified for the target variable.
Assignments are made in sequence through the list. Each assignment to a target is made according to the rules described in Language elements. If the number of targets is less than the number of values in the row, the SQLWARN3 field of the SQLCA is set to 'W'. There is no warning if there are more targets than the number of result columns. If a null value is assigned to a target variable, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the target and no more values are assigned to targets. Any values that have already been assigned to targets remain assigned. However, if LOB values are involved, there is a possibility that the corresponding target was modified, but the variable's contents are unpredictable.
If more than one assignment is included in the same assignment statement, all expressions are evaluated before the assignments are performed. For example, a reference to a variable in an expression always uses the value of the variable prior to any assignment in the assignment statement.
Normally, you use LOB locators to assign and retrieve data from LOB columns. However, because of compatibility rules, you can also use LOB locators to assign data to targets with other data types. For more information on using locators, see Saving storage when manipulating LOBs by using LOB locators.

- Default encoding scheme:
- The default encoding scheme for the data is the value in the bind option ENCODING, which is the option for application encoding. If this statement is used with functions such as LENGTH or SUBSTRING that are operating on LOB locators, and the LOB data that is specifies by the locator is in a different encoding scheme from the ENCODING bind option, LOB materialization and character conversion occur. To avoid LOB materialization and character conversion, select the LOB data from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table.
Examples
EXEC SQL VALUES(CURRENT PATH)
INTO :HV1; EXEC SQL VALUES(CURRENT MEMBER)
INTO :MEM; EXEC SQL VALUES (SUBSTR(:LOB1,1,35))
INTO :DETAILS; EXEC SQL SELECT SUBSTR(:LOB1,1,35)
INTO :DETAILS
FROM SYSIBM.SYSDUMMYU;
Example 4: Using a VALUES INTO
statement, retrieve the value of INTVAR1 into an element in array
MYINTARRAY1, which is indexed by the value of the expression INTCOL2+MYINTVAR+1.
VALUES INTVAR1 INTO MYINTARRAY1[INTCOL2+MYINTVAR+1];

