1.6.7 Assignment rules

The following table lists the assignment types.

Table 1-22 Assignment types

Assignment typeDescriptionSourceTarget
Retrieval assignmentRetrieves the value of a string.StringEmbedded variable, SQL variable, or SQL parameter
Sets the output parameter value of a procedure into an argument of the CALL statement.SQL parameterEmbedded variable, SQL variable, or SQL parameter
Retrieves the return value from the value expression specified in the RETURN statement of a function.Value expressionReturn value of a function
Storage assignmentAssigns a value to a string.Embedded variable, SQL variable, or SQL parameterString
Assigns a value from a CALL statement argument to an input parameter of a procedure.Value expressionSQL parameter
Assigns a value from a function call argument to the function parameter.Value expressionSQL parameter
Assigns a value using an assignment (SET) statement.SQL variable, SQL parameter, or embedded variableSQL variable, SQL parameter, or embedded variable
Organization of this subsection
(1) Fixed-length target
(2) If the target of assignment is variable-length data, large object data, or BINARY
(3) Rules on assignment between different character sets
(4) Rules on the structure of the source and target of an assignment

(1) Fixed-length target

If the target of an assignment is fixed-length character string data, mixed character string data, or national character string data, the applicable assignment rules vary with the length of the source data. The following table indicates the assignment rules for fixed-length target data.

Table 1-23 Assignment rules for fixed-length target data

Assignment typeData length
Source#1 > targetSource#1 = targetSource#1 < target
Retrieval assignment--# 2Y--#3
Storage assignmentNY--#3

Y: Assigned as is

--: Assigned left-justified according to length of target data

N: An error occurs.

#1: If the source is variable-length data, the length of the source data is its actual length.

#2: If an overflow occurs, the excess portion of the data is truncated on the right, and warning information is set in the SQLWARN1 parameter for the SQL communication area. If an indicator variable is specified, the length of data before truncation is set in the indicator variable.

#3: The trailing portion is space-filled on the right.

(2) If the target of assignment is variable-length data, large object data, or BINARY

If the target of an assignment is character string data, mixed character data, national character string data, large object data, or BINARY of variable length, the rules of assignment vary depending upon the length of the assignment source data. The following table indicates assignment rules that are applicable when the target of an assignment is variable-length data.

Table 1-24 Assignment rules for variable-length target data

Assignment typeData length
Source#1 > target#2Source#1[Figure] target#2
Retrieval assignment--#3--#4
Storage assignmentN--#4

--: Assigned left-justified according to length of source data2

N: An error occurs.

#1: If the source is variable-length data, the length of the source data is its actual length.

If the source is the embedded variable for a locator, the data length of the source is the actual length of the data that is assigned to the locator.

#2: The length of target data is equal to the maximum allowable length of variable-length data.

If the source is the embedded variable for a locator, the target data length takes one of the following lengths:

BLOB locator: 2147483647

BINARY locator: 2147483647

#3: If an overflow occurs, the excess portion of the data is truncated on the right, and the actual length is equal to the maximum allowable length of the target string. When this occurs, warning information is set in the SQLWARN1 parameter for the SQL communication area. If an indicator variable is specified, the length of data before truncation is set in the indicator variable.

#4: If the source is fixed-length data, the length of the source data is its actual length.

(3) Rules on assignment between different character sets

In general, assignment between different character sets is not permitted. However, the following table indicates when such assignment is possible.

Table 1-25 When assignment between different character sets is possible.

Source character setTarget character set
Default character setEBCDIKUTF-16
Default character setYY#1Y#2
EBCDIKY#3YN
UTF-16Y#4NY
Legend:
Y: Can be assigned.
N: Cannot be assigned.
#1
For assignments from the SJIS default character set into EBCDIK, the permissible assignment combinations are as follows:
Source value expressionTarget value expression
Character string literal or embedded variableAny
#2
For assignments from the UTF-8 default character set into UTF-16, the permissible assignment combinations are as follows:
Source value expressionTarget value expression
Character string literal or embedded variableAny
Any other value expressionEmbedded variable
#3
For assignments from EBCDIK into the SJIS default character set, the permissible assignment combinations are as follows:
Source value expressionTarget value expression
Value expressionEmbedded variable
#4
For assignments from UTF-16 into the UTF-8 default character set, the permissible assignment combinations are as follows:
Source value expressionTarget value expression
Embedded variableAny
Value expressionEmbedded variable

When the source and target character sets are different, assignment proceeds as follows:

  1. Retrieval assignments
    • Assignment is performed after conversion into the target character set. At this time, if the length of the converted character data is greater than the length of the target character field, the excess portion of the data is truncated on the right and warning information is assigned to the SQLWARN1 parameter of the SQL communication area. If the target is an indicator variable, the length (number of bytes) of data before truncation is set to the indicator variable.
    • If the target is fixed-length character data and the length of the source character data is less than the length of the target field, the assignment is performed and the remaining portion of the field is filled with the space character from the target character set.
  2. Storage assignments
    • Assignment is performed after conversion into the target character set. An error results if the length of the converted character data is greater than the length of the target character field.
    • If the target is fixed-length character data and the length of the source character data is less than the length of the target field, the assignment is performed and the remaining portion of the field is filled with the space character from the target character set.

(4) Rules on the structure of the source and target of an assignment

When the source and target have incompatible structures, an assignment may not work. The following table indicates the structure rules for the source and target of an assignment operation.

Table 1-26 Structure rules for the source and target of assignment operation

Source structureTarget structure
Simple StructureRepetition Structure
Simple structureYN
Repetition structureNY

Y: Assignment allowed

N: Error may result.

Note: Subscripted repetition columns are treated as simple structures.