The basic operations of SQL are assignment and comparison. Assignment operations are performed during the execution of CALL, INSERT, UPDATE, FETCH, SELECT, SET variable, and VALUES INTO statements. 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
UNION, concatenation, CASE expressions, and the CONCAT, VALUE, COALESCE,
IFNULL, MIN, and MAX scalar functions. The compatibility matrix is as
Table 11. Data Type Compatibility
|Operands||Binary Integer||Decimal Number4||Floating Point||Character String||Graphic String||Binary String||Date||Time||Timestamp||Distinct Type|
|Character String||No||No||No||Yes||Yes 5||No 3||1||1||1||2|
|Graphic String||No||No||No||Yes 5||Yes||No||No||No||No||2|
|Binary String||No||No||No||No 3||No||Yes||No||No||No||2|
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.
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. In the case of the assignment to a host variable, a positive value may be returned in the SQLCODE.
An error occurs if:
A warning occurs if:
|Note:||Decimal refers to both packed and zoned decimal.|
|Note:||When fetching decimal data from a file that was not created by an SQL CREATE TABLE statement, a decimal field may contain data that is not valid. In this case, the data will be returned as stored, without any warning or error message being issued. A table that is created by the SQL CREATE TABLE statement does not allow decimal data that is not valid.|
Floating-point numbers are approximations of real numbers. Hence, when a decimal or integer number is assigned to a floating-point column or variable, the result may not be identical to the original number.
The approximation is more accurate if the receiving column or variable is defined as double precision (64 bits) rather than single precision (32 bits).
When a decimal or floating-point number is assigned to a binary integer column or variable, the number is converted, if necessary, to the precision and the scale of the target. If the scale of the target is zero, the fractional part of the number is lost. The necessary number of leading zeros is added or eliminated, and, in the fractional part of the number, the necessary number of trailing zeros is added, or the necessary number of trailing digits is eliminated.
When a decimal number is assigned to a decimal column or variable,
the number is converted, if necessary, to the precision and the scale of the target. The necessary number of leading zeros is added or eliminated, and, in the fractional part of the number, the necessary number of trailing zeros is added, or the necessary number of trailing digits is eliminated.
When an integer is assigned to a decimal column or variable, the number is converted first to a temporary decimal number and then, if necessary, to the precision and scale of the target. If the scale of the integer is zero, the precision of the temporary decimal number is 5,0 for a small integer, 11,0 for a large integer, or 19,0 for a big integer.
When a floating-point number is assigned to a decimal column or variable, the number is first converted to a temporary decimal number of precision 31 and then, if necessary, truncated to the precision and scale of the target. In this conversion, the number is rounded (using floating-point arithmetic) to a precision of 31 decimal digits. As a result, a number less than 0.5*10-31 is reduced to 0. The scale is given the largest possible value that allows the whole part of the number to be represented without loss of significance.
Assignment to COBOL and RPG small or large integer host variables takes into account any scale specified for the host variable. However, assignment to integer host variables uses the full size of the integer. Thus, the value placed in the COBOL data item or RPG field may be larger than the maximum precision specified for the host variable.
In COBOL, for example, if COL1 contains a value of 12345, the statements:
01 A PIC S9999 BINARY. EXEC SQL SELECT COL1 INTO :A FROM TABLEX END-EXEC.
result in the value 12345 being placed in A, even though A has been defined with only 4 digits.
Notice that the following COBOL statement:
MOVE 12345 TO A.
results in 2345 being placed in A.
The following rules apply when both the source and the target are strings. When a datetime data type is involved, see Datetime Assignments.
There are two types of string assignments:
The rules differ for storage and retrieval assignment.
The basic rule is that the length of a string assigned to a column or parameter of a function or procedure must not be greater than the length attribute of the column or parameter. If the string is longer than the length attribute of that column, a negative SQLCODE is returned. (Trailing blanks are normally included in the length of the string. For storage assignments, however, trailing blanks are not included in the length of the string.)
For a description of the SQLCA, see Appendix B, SQL Communication Area.
When a string is assigned to a fixed-length string column or parameter and the length of the string is less than the length attribute of the target, the string is padded on the right with the necessary number of single-byte, double-byte, or UCS-2 blanks.15 The pad character is always a blank, even for bit data.
The length of a string assigned to a host variable can be greater than the length attribute of the host variable. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the string is truncated on the right by the necessary number of characters. When this occurs, the value 'W' is assigned to the SQLWARN1 field of the SQLCA. Furthermore, if an indicator variable is provided, it is set to the original length of the string. If only the NUL-terminator is truncated for a C NUL-terminated host variable and the *NOCNULRQD option was specified on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO) on the SET OPTION statement), the value of 'N' is assigned to the SQLWARN1 field of the SQLCA and a NUL is not placed in the variable.
When a string is assigned to a fixed-length variable and the length of the string is less than the length attribute of the target, the string is padded on the right with the necessary number of single-byte, double-byte, or UCS-2 blanks.15 The pad character is always a blank, even for bit data.
When a string of length n is assigned to a varying-length string variable with a maximum length greater than n, the characters after the nth character of the variable are undefined.
If a string contains mixed data, the assignment rules may require truncation within a sequence of double-byte codes. To prevent the loss of the shift-in character that ends the double-byte sequence, additional characters may be truncated from the end of the string, and a shift-in character added. In the truncated result, there is always an even number of bytes between each shift-out character and its matching shift-in character.
When a string of length n is assigned to a C NUL-terminated string variable with a length greater than n+1:
A string assigned to a column or host variable is first converted, if necessary, to the coded character set of the target. Character conversion is necessary only if all of the following are true:
An error occurs if:
A warning occurs if:
A value assigned to a DATE column must be a date or a valid string representation of a date. A date can only be assigned to a DATE column, a character-string column, a character-string variable or an ILE RPG/400 timestamp variable. A value assigned to a TIME column must be a time or a valid string representation of a time. A time can only be assigned to a TIME column, a character-string column, a character-string variable or an ILE RPG/400 timestamp variable. A value assigned to a TIMESTAMP column must be a timestamp or a valid string representation of a timestamp. A timestamp can only be assigned to a TIMESTAMP column, a character-string column, a character-string variable or an ILE RPG/400 timestamp variable.
When a datetime value is assigned to a character-string variable or column, it is converted to its string representation. Leading zeros are not omitted from any part of the date, time, or timestamp. The required length of the target varies depending on the format of the string representation. If the length of the target is greater than required, it is padded on the right with blanks. If the length of the target is less than required, the result depends on the type of datetime value involved and on the type of target.
The assignment of a value to a DataLink column results in the establishment of a link to a file unless the linkage attributes of the value are empty or the column is defined with NO LINK CONTROL. In cases where a linked value already exists in the column, that file is unlinked. Assigning a null value where a linked value already exists also unlinks the file associated with the old value.
If the application provides the same data location as already exists in the column, the link is retained. There are two reasons that this might be done:
A DataLink value may be assigned to a column by using the DLVALUE scalar function. The DLVALUE scalar function creates a new DataLink value which can then be assigned a column. Unless the value contains only a comment or the URL is exactly the same, the act of assignment will link the file.
When assigning a value to a DataLink column, the following error conditions can occur:
Note that the size of a URL parameter or function result is the same on both input or output and is bound by the length of the DataLink column. However, in some cases the URL value returned has an access token attached. In situations where this is possible, the output location must have sufficient storage space for the access token and the length of the DataLink column. Hence, the actual length of the comment and URL in its fully expanded form provided on input should be restricted to accommodate the output storage space. If the restricted length is exceeded, this error is raised.
When the assignment is also creating a link, the following errors can occur:
Note that this error will be raised even if the link is to a different relational database.
In addition, when the assignment removes an existing link, the following errors can occur:
A DataLink value may be retrieved from the database through the use of scalar functions (such as DLLINKTYPE and DLURLPATH). The results of these scalar functions can then be assigned to host variables.
Note that usually no attempt is made to access the file server at retrieval time. 16 It is therefore possible that subsequent attempts to access the file server through file system commands might fail.
A warning may be returned when retrieving a DataLink value because the table is in link pending state.
The rules that apply to the assignments of user-defined types to host variables are different than the rules for all other assignments that involve distinct types.
The assignment of a distinct type to a host variable is based on the source data type of the distinct type. Therefore, the value of a distinct type is assignable to a host variable only if the source data type of the distinct type is assignable to the host variable.
Assume that distinct type AGE was created with the following SQL statement and column STU_AGE in table STUDENTS was defined with that distinct type. Using the CL_SCHED table, select all the classes (CLASS_CODE) that start (STARTING) later today. Today's classes have a value of 3 in the DAY column.
CREATE DISTINCT TYPE AGE AS SMALLINT WITH COMPARISONS
Next, consider this valid assignment of a student's age to host variable HV_AGE, which has an INTEGER data type.
SELECT STU_AGE INTO :HV_AGE FROM STUDENTS WHERE STU_NUMBER = 200
The distinct type value is assignable to the host variable HV_AGE because the source data type of the distinct type (SMALLINT) is assignable to the host variable (INTEGER). If distinct type AGE had been sourced on a character data type such as CHAR(5), the above assignment would be invalid because a character type cannot be assigned to an integer type.
A distinct type can be either the source or target of an assignment. Assignment is based on whether the data type of the value to be assigned is castable to the data type of the target. Casting Between Data Types shows which casts are supported when a distinct type is involved. Therefore, a distinct type value can be assigned to any target other than a host variable when:
Any value can be assigned to a distinct type when:
Assume that the source data type for distinct type AGE is SMALLINT:
CREATE DISTINCT TYPE AGE AS SMALLINT WITH COMPARISONS
Next, assume that two tables TABLE1 and TABLE2 were created with four identical column descriptions:
AGECOL AGE SMINTCOL SMALLINT INTCOL INTEGER DECCOL DEC(6,2)
Using the following SQL statement and substituting various values for X and Y to insert values into various columns of TABLE1 from TABLE2, Table 12 shows whether the assignments are valid.
INSERT INTO TABLE1 (Y) SELECT X FROM TABLE2
Table 12. Assessment of various assignments (for example on INSERT)
|AGECOL||AGECOL||Yes||Source and target are same distinct type|
|SMINTCOL||AGECOL||Yes||SMALLINT can be cast to AGE (because AGE's source type is SMALLINT)|
|INTCOL||AGECOL||Yes||INTEGER can be cast to AGE (because AGE's source type is SMALLINT)|
|DECCOL||AGECOL||No||DECIMAL cannot be cast to AGE|
|AGECOL||SMINTCOL||Yes||AGE can be cast to its source type SMALLINT|
|AGECOL||INTCOL||No||AGE cannot be cast to INTEGER|
|AGECOL||DECCOL||No||AGE cannot be cast to DECIMAL|
Numbers are compared algebraically; that is, with regard to sign. For example, negative 2 is less than +1.
If one number is an integer and the other number is decimal, the comparison is made with a temporary copy of the integer, which has been converted to decimal.
When decimal or nonzero scale binary numbers with different scales are compared, the comparison is made with a temporary copy of one of the numbers that has been extended with trailing zeros so that its fractional part has the same number of digits as the other number.
If one number is floating point and the other number is integer, decimal, or single-precision floating point, the comparison is made with a temporary copy of the second number converted to a double-precision floating-point number. However, if a single-precision floating-point column is compared to a constant and the constant can be represented by a single-precision floating-point number, the comparison is made with a single-precision form of the constant.
Two floating-point numbers are equal only if the bit configurations of their normalized forms are identical.
Binary string comparisons always use a sort sequence of *HEX and the corresponding bytes of each string are compared. Additionally, two binary strings are equal only if the length of the two strings is identical.
Character and UCS-2 graphic string comparisons use the sort sequence in effect when the statement is executed for all SBCS data and the single-byte portion of mixed data. If the sort sequence is *HEX, the corresponding bytes of each string are compared. For all other sort sequences, the corresponding bytes of the weighted value of each string are compared. If the strings have different lengths, a temporary copy of the shorter string is padded on the right with blanks before comparison. The padding makes each string the same length. The pad character is always a blank, regardless of the sort sequence. For bit data, the pad character is also a blank. For DBCS graphic data, the pad character is a DBCS blank (x'4040'). For UCS-2 graphic data, the pad character is a UCS-2 blank. 17
Two strings are equal if any of the following are true:
An empty string is equal to a blank string. The relationship between two unequal strings is determined by a comparison of the first pair of unequal bytes (or bytes of the weighted value) from the left end of the string. This comparison is made according to the sort sequence in effect when the statement is executed.
Two varying-length strings with different lengths are equal if they differ only in the number of trailing blanks. In operations that select one value from a set of such values, the value selected is arbitrary. The operations that can involve such an arbitrary selection are DISTINCT, MAX, MIN, UNION and references to a grouping column. See the description of GROUP BY for further information about the arbitrary selection involved in references to a grouping column.
When two strings are compared, one of the strings is first converted, if necessary, to the coded character set of the other string. Character conversion is necessary only if all of the following are true:
If two strings with different encoding schemes are compared and the
operands are the same type, any necessary conversion applies to the string as
Table 13. Selecting the Encoding Scheme for Character Conversion
|First Operand||Second Operand|
|SBCS Data||DBCS Data||Mixed Data||UCS-2 Data|
|SBCS Data||See below||Second||Second||Second|
|DBCS Data||First||See below||Second||Second|
|Mixed Data||First||First||See below||Second|
|UCS-2 Data||First||First||First||See below|
Otherwise, the string selected for conversion depends on the type of each
operand. The following table shows which operand is selected for
conversion, given the operand types:
Table 14. Selecting the Operand for Character Conversion
|First Operand||Second Operand|
|Column Value||Derived Value||Special Register||Constant||Host Variable|
A host variable containing data in a foreign encoding scheme is always effectively converted to the native encoding scheme before it is used in any operation. The above rules are based on the assumption that this conversion has already occurred.
An error occurs if a character of the string cannot be converted or the CCSID Conversion Selection Table is used but does not contain any information about the pair of CCSIDs. A warning occurs if a character of the string is converted to the substitution character.
A DATE, TIME, or TIMESTAMP value can be compared either with another value of the same data type or with a string representation of that data type. All comparisons are chronological, which means the farther a point in time is from January 1, 0001, the greater the value of that point in time.
Comparisons involving TIME values and string representations of time values always include seconds. If the string representation omits seconds, zero seconds are implied. The time 24:00:00 compares greater than the time 00:00:00.
Comparisons involving TIMESTAMP values are chronological without regard to representations that might be considered equivalent. Thus, the following predicate is true:
TIMESTAMP('1990-02-23-00.00.00') > '1990-02-22-24.00.00'
A value with a distinct type can be compared only to another value with exactly the same distinct type.
For example, assume that distinct type YOUTH and table CAMP_DB2_ROSTER table were created with the following SQL statements:
CREATE DISTINCT TYPE YOUTH AS INTEGER WITH COMPARISONS CREATE TABLE CAMP_DB2_ROSTER ( NAME VARCHAR(20), ATTENDEE_NUMBER INTEGER NOT NULL, AGE YOUTH, HIGH_SCHOOL_LEVEL YOUTH)
The following comparison is valid because AGE and HIGH_SCHOOL_LEVEL have the same distinct type:
SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > HIGH_SCHOOL_LEVEL
The following comparison is not valid:
SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > ATTENDEE_NUMBER
However, AGE can be compared to ATTENDEE_NUMBER by using a cast function or CAST specification to cast between the distinct type and the source type. All of the following comparisons are valid:
SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > YOUTH(ATTENDEE_NUMBER) SELECT * FROM CAMP_DB2_ROSTER WHERE AGE > CAST( ATTENDEE_NUMBER AS YOUTH) SELECT * FROM CAMP_DB2_ROSTER WHERE INTEGER(AGE) > ATTENDEE_NUMBER SELECT * FROM CAMP_DB2_ROSTER WHERE CAST(AGE AS INTEGER) > ATTENDEE_NUMBER