SET variable

Invocation

This statement can be embedded in an application program. It is an executable statement that can be dynamically prepared if the all variables being set are global variables. It must not be specified in REXX.

Authorization

If a row-fullselect is specified, see fullselect for an explanation of the authorization required for each subselect.

If a global variable is specified on the left hand side of the assignment, the privileges held by the authorization ID of the statement must include:

• The WRITE privilege on the global variable.

Description

variable, ...

Identifies one or more variables or host structures that must be declared in accordance with the rules for declaring variables (see References to host variables). A host structure is logically replaced by a list of variables that represent each of the elements of the host structure.

The value to be assigned to each variable can be specified immediately following the variable, for example, variable = expression, variable = expression. Or, sets of parentheses can be used to specify all the variables and then all the values, for example, (variable, variable) = (expression, expression).

The data type of each variable must be compatible with its corresponding result column. Each assignment is made according to the rules described in Assignments and comparisons. The number of variables specified to the left of the equal operator must equal the number of values in the corresponding result specified to the right of the equal operator. If the value is null, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the variable, and no more values are assigned to variables. Any values that have already been assigned to variables remain assigned.

If an error occurs as the result of an arithmetic expression in the expression or SELECT list of the subselect (division by zero, or overflow) or a character conversion error occurs, the result is the null value. As in any other case of a null value, an indicator variable must be provided. The value of the variable is undefined. In this case, however, the indicator variable is set to -2. Processing of the statement continues as if the error had not occurred. (However, a warning is returned.) If you do not provide an indicator variable, an error is returned. It is possible that some values have already been assigned to variables and will remain assigned when the error occurs.

expression

Specifies the new value of the variable. The expression is any expression of the type described in Expressions. It must not include a column name.

NULL

Specifies that the new value for the variable is the null value.

DEFAULT

Specifies that the new value for the variable is its initial default value. DEFAULT can only be assigned to a global variable. Only one variable can be assigned in the SET statement when DEFAULT is used.

row-fullselect

A fullselect that returns a single result row. The result column values are assigned to each corresponding variable. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.

WITH common-table-expression

Specifies a common table expression. For an explanation of common table expression, see common-table-expression.

fullselect

A fullselect that returns a single result row. The result column values are assigned to each corresponding variable. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.

Notes

Variable assignment: If the specified variable is character and is not large enough to contain the result, a warning (SQLSTATE 01004) is returned (and 'W' is assigned to SQLWARN1 in the SQLCA). The actual length of the result is returned in the indicator variable associated with the variable, if an indicator variable is provided.

If the specified variable is a C NUL-terminated variable and is not large enough to contain the result and the NUL-terminator:

• If the *CNULRQD option is specified on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*YES) on the SET OPTION statement), the following occurs:
  • The result is truncated.
  • The last character is the NUL-terminator.
  • The value ‘W' is assigned to SQLWARN1 in the SQLCA.
• If the *NOCNULRQD option on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO) on the SET OPTION statement) is specified, the following occurs:
  • The NUL-terminator is not returned.
  • The value ‘N' is assigned to SQLWARN1 in the SQLCA.

Multiple assignments: If more than one assignment is included in the same SET statement, all expressions and row-fullselects are completely evaluated before the assignments are performed. Thus, references to a target variable in an expression or row-fullselect are always the value of the target variable prior to any assignment in the SET statement.

Examples

Example 1: Assign the value of the CURRENT PATH special register to host variable HV1.

Example 2: 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.