SQL Reference

Assignments and Comparisons

The basic operations of SQL are assignment and comparison. Assignment operations are performed during the execution of INSERT, UPDATE, FETCH, SELECT INTO, VALUES INTO and SET transition-variable statements. Arguments of functions are also assigned when invoking a function. 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 the data type of the operands involved must be compatible. The compatibility rule also applies to set operations (see Rules for Result Data Types). The compatibility matrix is as follows.

Table 7. Data Type Compatibility for Assignments and Comparisons

Operands	Binary Integer	Decimal Number	Floating Point	Character String	Graphic String	Date	Time	Time- stamp	Binary String	UDT
Binary Integer	Yes	Yes	Yes	No	No	No	No	No	No	2
Decimal Number	Yes	Yes	Yes	No	No	No	No	No	No	2
Floating Point	Yes	Yes	Yes	No	No	No	No	No	No	2
Character String	No	No	No	Yes	No	1	1	1	No 3	2
Graphic String	No	No	No	No	Yes	No	No	No	No	2
Date	No	No	No	1	No	Yes	No	No	No	2
Time	No	No	No	1	No	No	Yes	No	No	2
Timestamp	No	No	No	1	No	No	No	Yes	No	2
Binary String	No	No	No	No 3	No	No	No	No	Yes	2
UDT	2	2	2	2	2	2	2	2	2	Yes
Note: 1 The compatibility of datetime values and character strings is limited to assignment and comparison: Datetime values can be assigned to character string columns and to character string variables as explained in Datetime Assignments. A valid string representation of a date can be assigned to a date column or compared with a date. A valid string representation of a time can be assigned to a time column or compared with a time. A valid string representation of a timestamp can be assigned to a timestamp column or compared with a timestamp. 2 A user-defined distinct type (UDDT) value is only comparable to a value defined with the same UDDT. In general, assignments are supported between a distinct type value and its source data type. A user-defined structured type is not comparable and can only be assigned to an operand of the same structured type or one of its supertypes. For additional information see User-defined Type Assignments. 3 Note that this means that character strings defined with the FOR BIT DATA attribute are also not compatible with binary strings. 4 A DATALINK operand can only be assigned to another DATALINK operand. The DATALINK value can only be assigned to a column if the column is defined with NO LINK CONTROL or the file exists and is not already under file link control. 5 For information on assignment and comparison of reference types see Reference Type Assignments and Reference Type Comparisons.

A basic rule for assignment operations is that a null value cannot be assigned to a column that cannot contain null values, nor to a host variable that does not have an associated indicator variable. (See References to Host Variables for a discussion of indicator variables.)