VALUES INTO statement

The VALUES INTO statement assigns one or more values to variables.

Invocation for VALUES INTO

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

Authorization for VALUES INTO

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 or an element of an array 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

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

  • The UPDATE privilege on the table or view on which the trigger that contains the assignment statement is defined
  • The UPDATE privilege on the column corresponding to the transition variable to be assigned a value
  • Ownership of the table or view on which the trigger that contains the assignment statement is defined
  • DBADM authority on the database that contains the table on which the trigger that contains the assignment statement is defined
  • 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 for VALUES INTO

Read syntax diagramSkip visual syntax diagram VALUES expressionNULL(,expressionNULL) INTO ,target-variablearray-variable[array-index]1
target-variable
Read syntax diagramSkip visual syntax diagramglobal-variable-namehost-variable-nameSQL-parameter-nameSQL-variable-nametransition-variable-name
Notes:
  • 1 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 for VALUES INTO

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. 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.
transition-variable-name
Identifies the column that is to be updated in the transition table. 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 [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 for VALUES INTO

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 in SQL. 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 for VALUES INTO

Example 1: Assign the value of the CURRENT PATH special register to host variable HV1. 
   EXEC SQL VALUES(CURRENT PATH)
            INTO :HV1;
Example 2: Assign the value of the CURRENT MEMBER special register to host variable MEM. 
   EXEC SQL VALUES(CURRENT MEMBER)
            INTO :MEM;
Example 3: Assume that LOB locator LOB1 is associated with a CLOB value. Assign a portion of the CLOB value to host variable DETAILS using the LOB locator. 
   EXEC SQL VALUES (SUBSTR(:LOB1,1,35))
            INTO :DETAILS;
If the LOB data that is specified by the LOB locator LOB1 is in a different encoding scheme from the value of the ENCODING bind option, and you want to avoid LOB materialization and character conversion, use the following statement instead of the VALUES INTO statement: 
   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];