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 for numeric data types.

Table 1. Compatibility of data types for assignments and comparisons with numeric data types. Y indicates that the data types are compatible. N indicates no compatibility. For any number in a column, read the corresponding note at the bottom of the table.
Operand	Binary integer	Decimal number	Floating point	Decimal floating point
Binary integer	Y	Y	Y	Y
Decimal number	Y	Y	Y	Y
Floating point	Y	Y	Y	Y
Decimal floating point	Y	Y	Y	Y
Character string	¹	¹	¹	¹
Graphic string	²	²	²	²
Binary string	N	N	N	N
Date	N	N	N	N
Time	N	N	N	N
Timestamp without time zone	N	N	N	N
Timestamp with time zone	N	N	N	N
Row ID	N	N	N	N
Distinct type	³	³	³	³
XML⁴	N	N	N	N
Notes: LOBs and bit data are not supported. LOBs are not supported. A value with a distinct type is comparable only to a value that is defined with the same distinct type. In general, DB2® supports assignments between a distinct type value and its source data type. For additional information, see Distinct type assignments. For comparison, XML can only be compared using the XMLEXISTS and NULL predicates.

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

Table 2. Compatibility of data types for assignments and comparisons with string data types. Y indicates that the data types are compatible. N indicates no compatibility. For any number in a column, read the corresponding note at the bottom of the table.
Operand	Character string	Graphic string	Binary string
Binary integer	⁷	⁷	N
Decimal number	⁷	⁷	N
Floating point	⁷	⁷	N
Decimal floating point	⁷	⁷	N
Character string	Y	Y^4,5	N³
Graphic string	Y^4,5	Y	N
Binary string	N³	N	Y
Date	¹	^1,4	N
Time	¹	^1,4	N
Timestamp without time zone	¹	^1,4	N
Timestamp with time zone	¹	^1,4	N
Row ID	N	N	N
Distinct type	²	²	²
XML⁶	N	N	N
Notes: The compatibility of datetime values is limited to assignment and comparison: Datetime values can be assigned to string columns and to string variables that are not LOB values, as explained in Datetime assignments. A valid string representation of a date can be assigned to a date column or compared to a date. A valid string representation of a time can be assigned to a time column or compared to a time. A valid string representation of a timestamp(without or with time zone) can be assigned to a timestamp column(without or with time zone) or compared to a timestamp(without or with time zone). A value with a distinct type is comparable only to a value that is defined with the same distinct type. In general, DB2 supports assignments between a distinct type value and its source data type. For additional information, see Distinct type assignments. All character strings, even those with subtype FOR BIT DATA, are not compatible with binary strings. 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. Character and graphic strings, including LOBs, can be assigned to XML columns. However, XML cannot be assigned to a character or graphic string column. For comparison, XML can only be compared using the XMLEXISTS and NULL predicates. LOBs and bit data are not supported.

The following table shows the compatibility of data types for assignments and comparisons for date, time, and timestamp data types.

Table 3. Compatibility of data types for assignments and comparisons with date, time, and timestamp (without or with time zone) data types. Y indicates that the data types are compatible. N indicates no compatibility. For any number in a column, read the corresponding note at the bottom of the table.
Operand	Date	Time	Timestamp without time zone	Timestamp with time zone
Binary integer	N	N	N	N
Decimal number	N	N	N	N
Floating point	N	N	N	N
Decimal floating point	N	N	N	N
Character string	Y	Y	Y	Y
Graphic string	²	²	²	²
Binary string	N	N	N	N
Date	Y	N	N	N
Time	N	Y	N	N
Timestamp without time zone	N	N	Y	Y
Timestamp with time zone	N	N	Y	Y
Row ID	N	N	N	N
Distinct type	¹	¹	¹	¹
XML³	N	N	N	N
Notes: A value with a distinct type is comparable only to a value that is defined with the same distinct type. In general, DB2 supports assignments between a distinct type value and its source data type. For additional information, see Distinct type assignments. On assignment and comparison from Graphic to Character, the resulting length in bytes is `3 * (LENGTH(graphic-string))`, depending on the CCSIDs. For comparison, XML can only be compared using the XMLEXISTS and NULL predicates.

The following table shows the compatibility of data types for assignments and comparisons for row ID, XML, and distinct data types.

Table 4. Compatibility of data types for assignments and comparisons with row ID, XML, and distinct data types. Y indicates that the data types are compatible. N indicates no compatibility. For any number in a column, read the corresponding note at the bottom of the table.
Operand	Row ID	XML²	Distinct type
Binary integer	N	N	¹
Decimal number	N	N	¹
Floating point	N	N	¹
Decimal floating point	N	N	¹
Character string	N	N	¹
Graphic string	N	N	¹
Binary string	N	N	¹
Date	N	N	¹
Time	N	N	¹
Timestamp without time zone	N	N	¹
Timestamp with time zone	N	N	¹
Row ID	Y	N	¹
Distinct type	¹	N	Y¹
XML²	N	Y	N
Notes: A value with a distinct type is comparable only to a value that is defined with the same distinct type. In general, DB2 supports assignments between a distinct type value and its source data type. For additional information, see Distinct type assignments. 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.

Numeric assignments
The basic rule for numeric assignments is that the whole part of a decimal or integer number cannot be truncated. If necessary, the fractional part of a decimal number is truncated.
String assignments
There are two types of string assignments; storage assignment and retrieval assignment.
Datetime assignments
A string value that is assigned to a date, time, or timestamp column, variable, or parameter must be a valid string representation of a date, a time, or a timestamp.
Row ID assignments
A row ID value can be assigned only to a column, parameter, or host variable with a row ID data type.
XML assignments
XML data can be assigned to a column, but when the target is not a column, the XML data type can only be assigned to another XML data type.
Distinct type assignments
The rules that apply to the assignments of distinct types to host variables are different than the rules for all other assignments that involve distinct types.
Assignments to LOB locators
When a LOB locator is used, it can refer only to LOB data. If a LOB locator is used for the first fetch of a cursor, LOB locators must be used for all subsequent fetches.
Numeric comparisons
Numbers are compared algebraically, that is, with regard to sign. For example, -2 is less than +1. When numbers of different data types are compared, certain rules are in effect as to how the comparison is performed.
String comparisons
String comparisons can occur with binary string, character strings, and graphic strings.
Datetime comparisons
A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type. Additionally, a TIMESTAMP WITHOUT TIME ZONE value can be compared with a TIMESTAMP WITH TIME ZONE value.
Row ID comparisons
A value with a row ID type can only be compared to another row ID value.
XML comparisons
The XML data type cannot be directly compared to any data type, including the XML data type. The method for doing comparison is through the use of the XMLEXISTS predicate.
Conversion rules for comparisons
When two strings are compared, one of the strings is first converted, if necessary, to the coded character set of the other string. Conversion is necessary only if certain rules apply.
Distinct type comparisons
A value with a distinct type can only be compared to another value with exactly the same type because distinct types have strong typing, which means that a distinct type is compatible only with its own type.

Parent topic: Language elements