Assignment statement Format 2 (Assign a value to an embedded variable or a ? parameter)
Function
Assigns a value to an embedded variable or a ? parameter.
Privileges
Format: Embedded-variable, or assigning to a ? parameter
SET assign-to = assignment-value
assign-to::= {:embedded-variable [:indicator-variable] | ?-parameter}
assignment-value::= {:embedded-variable [:indicator-variable] AS data-type
| ?-parameter AS data-type
| LENGTH (value-expression)
| SUBSTR (value-expression-1, value-expression-2[, value-expression-3])
|POSITION(value-expression-1 IN value-expression-2 [FROM value-expression-3])} |
Operands
- assign-to::= {:embedded-variable [:indicator-variable] | ?-parameter}
Specifies the embedded variable or the ? parameter into which a value is to be assigned.
- assignment-value::= {:embedded-variable [:indicator-variable] AS data-type
| ?-parameter AS data-type
| LENGTH (value-expression)
| SUBSTR (value-expression-1, value-expression-2 [, value-expression-3])
| POSITION (value-expression-1 IN value-expression-2 [FROM value-expression-3]) }
Specifies the value to be assigned.
- :embedded-variable [:indicator-variable] AS data-type
- The only data types that can be specified are the BLOB and the BINARY types. In the AS clause, specify the data type of the embedded variable. An error can occur if, in this operation, the actual length of the data (for a locator, the actual length of the data allocated to the locator) given by embedded-variable is greater than the maximum length of the data type specified in the AS clause.
- ?-parameter AS data-type
- The only data types that can be specified are the BLOB and the BINARY types. In the AS clause, specify the data type of ?-parameter. An error can occur if, in this operation, the actual length of the data (for a locator, the actual length of the data allocated to the locator) given by ?-parameter is greater than the maximum length of the data type specified in the AS clause.
- LENGTH (value-expression)
- SUBSTR (value-expression-1, value-expression-2, [value-expression-3])
- POSITION (value-expression-1 IN value-expression-2 [FROM value-expression-3])
- The only data types that can be specified in value-expression for the scalar function LENGTH, in value-expression-1 for the scalar function SUBSTR, or in value-expression-2 for the scalar function POSITION are the BLOB and the BINARY types. The only items that can be specified are embedded-variable and ?-parameter For other specification methods, see the rules on the individual scalar functions.
Common rules
- The data type of the assignment target must be compatible with the data type of the value being assigned.
- If the data type of the assignment target is different from that of the value being assigned, a type conversion is performed. If they are of the same data type, the assigned value is directly assigned to the assignment target.
- The following items cannot be specified in a value expression specified as an assignment value:
- Column specification
- Component specification
- Scalar subquery
Notes
- Format 2 of the assignment statement cannot be specified in an SQL routine. For specifying an assignment statement in an SQL routine, Format 1 of the assignment statement should be used.
Examples
Assign a part of the BLOB data allocated to the embedded variable (XLOC) of the BLOB locator to an embedded variable (XDATA) of the BLOB type.
SET :XDATA = SUBSTR(:XLOC AS BLOB(1M), 100, 1024)