Assignment and comparison

The basic operations of SQL are assignment and comparison.

Assignment operations are performed during the execution of statements such as CALL, INSERT, UPDATE, MERGE, FETCH, SELECT INTO, SET host-variable or SET assignment-statement, and VALUES INTO statements. In addition, when a function is invoked or a stored procedure is called, the arguments of the function or stored procedure are assigned. Comparison operations are performed during the execution of statements that include predicates and other language elements such as MAX, MIN, DISTINCT, GROUP BY, and ORDER BY.

The basic rule for both operations is that data types of the operands must be compatible. The compatibility rule also applies to other operations that are described under Rules for result data types.

The following table shows the compatibility of data types for assignments and comparisons.

Table 1. Data Type Compatibility for Assignments and Comparisons
Operand	Binary integer	Decimal number	Floating point	Decimal floating point	Character string	Graphic string	Binary string	Date	Time	Timestamp without time zone	Timestamp with time zone	Row ID	User-defined type	XML⁸
Binary integer	Yes	Yes	Yes	Yes	¹	¹	No	No	No	No	No	No	²	No
Decimal number	Yes	Yes	Yes	Yes	¹	¹	No	No	No	No	No	No	²	No
Floating point	Yes	Yes	Yes	Yes	¹	¹	No	No	No	No	No	No	²	No
Decimal floating point	Yes	Yes	Yes	Yes	¹	¹	No	No	No	No	No	No	²	No
Character string	¹	¹	¹	¹	Yes	Yes ^3,4	No ⁵	Yes ⁷	Yes ⁷	Yes⁷	Yes ⁷	No	²	No
Graphic string	⁶	⁶	⁶	⁶	Yes ^3,4	Yes	No	^{3, 7}	^{3, 7}	^{3, 7}	^{3, 7}	No	²	No
Binary string	No	No	No	No	No ⁵	No	Yes	No	No	No	No	No	²	No
Date	No	No	No	No	⁷	^3,7	No	Yes	No	No	No	No	²	No
Time	No	No	No	No	⁷	^3,7	No	No	Yes	No	No	No	²	No
Timestamp without time zone	No	No	No	No	⁷	^3,7	No	No	No	Yes	Yes	No	²	No
Timestamp with time zone	No	No	No	No	⁷	^3,7	No	No	No	Yes	Yes	No	²	No
Row ID	No	No	No	No	No	No	No	No	No	No	No	Yes	²	No
User-defined type	²	²	²	²	²	²	²	²	²	²	²	²	Yes²	No
XML⁸	No	No	No	No	No	No	No	No	No	No	No	No	No	Yes
LOBs and bit data are not supported. The compatibility rules for user-defined types are as follows: A user-defined distinct type value is only comparable to a value that is defined with the same user-defined distinct type. In general, assignments are supported between a distinct type value and its source data type. A user-defined array type value is only comparable to a value that is defined with the same user-defined array type. This means that in general, an ordinary array type is not compatible with an associative array type. The following exceptions apply only to a CALL statement for a remote stored procedure: A source value that is an ordinary array with an integer index can be specified for a target that is defined as an associative array, if the definitions of the array elements of the two arrays are compatible. Db2 transforms the ordinary array into an associative array with an integer index, preserving the ordering of the elements in the original ordinary array. However, if the associative array is defined with a VARCHAR index, an SQL error code is returned. A source value that is an associative array can be specified for a target defined as an ordinary array . Db2 transforms the associative array into an ordinary array, by assigning the values of the array elements in the associative array in the same order in the target ordinary array, and assigning appropriate index values. For additional information, see User-defined type assignments. On assignment and comparison from Graphic to Character, the resulting length in bytes is `3 * (LENGTH(graphic-string))`, depending on the CCSIDs. Character strings with subtype FOR BIT DATA are not compatible with Graphic Data. All character strings, even those with subtype FOR BIT DATA, are not compatible with binary strings. LOBs are not supported. The compatibility of datetime and string values is subject to the following considerations: Datetime values can be assigned to string columns and to string variables that are not LOB values. A valid string representation of a datetime value can be assigned to a datetime column or variable, or be compared to a datetime value, as explained in Datetime assignments. In some situations an assignment of a string value to a datetime variable might not be supported, such as in the following examples: An assignment of a string representation of a datetime value to a datetime host variable. An assignment in SQL PL when a datetime SQL variable or SQL parameter is the target of a SELECT INTO statement after a SET CURRENT PACKAGESET statement is issued. Character and graphic strings, including LOBs, can be assigned to XML columns. For comparison, XML can only be compared using the XMLEXISTS and NULL predicates.

Compatibility with a column that has a field procedure is determined by the data type of the column, which applies to the decoded form of its values.

A basic rule for assignment operations is that a null value cannot be assigned to:

A column that cannot contain null values
A non-Java™ host variable that does not have an associated indicator variable
For a host variable that does have an associated indicator variable, a null value is assigned by setting the indicator variable to a negative value. See Host variables for a discussion of indicator variables.
A Java host variable that is a primitive type
For a Java host variable that is not a primitive type, the value of that variable is set to a Java null value.