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, EXCEPT, INTERSECT, concatenation, CASE expressions, and the CONCAT, VALUE, COALESCE, IFNULL, MIN, and MAX scalar functions. The compatibility matrix is as follows:
|
Operands |
Binary Integer |
Decimal Number |
Floating Point |
Character String |
Graphic String |
Binary String |
Date |
Time |
Timestamp |
DataLink |
Row ID |
Distinct Type |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Binary Integer | Yes | Yes | Yes | Yes | 1 | No | No | No | No | No | No | 4 |
| Decimal Number 5 | Yes | Yes | Yes | Yes | 1 | No | No | No | No | No | No | 4 |
| Floating Point | Yes | Yes | Yes | Yes | 1 | No | No | No | No | No | No | 4 |
| Character String | Yes | Yes | Yes | Yes | 1 | 2 | 3 | 3 | 3 | No | No | 4 |
| Graphic String | 1 | 1 | 1 | 1 | Yes | No | 1 3 | 1 3 | 1 3 | No | No | 4 |
| Binary String | No | No | No | 2 | No | Yes | No | No | No | No | No | 4 |
| Date | No | No | No | 3 | 1 3 | No | Yes | No | No | No | No | 4 |
| Time | No | No | No | 3 | 1 3 | No | No | Yes | No | No | No | 4 |
| Timestamp | No | No | No | 3 | 1 3 | No | No | No | Yes | No | No | 4 |
| DataLink | No | No | No | No | No | No | No | No | No | 6 | No | 4 |
| Row ID | No | No | No | No | No | No | No | No | No | No | 7 | 4 |
| Distinct Type | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
|
Notes:
|
||||||||||||
A basic rule for assignment operations is that a null value cannot be assigned to:
See References to host variables for a discussion of indicator variables.
For any comparison that involves null values, see the description of the comparison operation for information about the specific handling of null values.
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.
An error occurs if:
A warning occurs if:
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 floating-point or decimal 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 the necessary number of trailing zeros in the fractional part of the number is added or 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 the necessary number of trailing zeros in the fractional part of the number is added or 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 63 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 63 decimal digits. As a result, a number less than 0.5*10-63 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.
When a string is assigned to a numeric data type, it is converted to the target numeric data type using the rules for a CAST specification. For more information, see CAST specification.
There are two types of string assignments:
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 or parameter, a error is returned. Trailing hexadecimal zeroes (X'00') are normally included in the length of the string. For storage assignments, however, trailing hexadecimal zeroes are not included in the length of the string.
When a string is assigned to a fixed-length binary-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 hexadecimal zeroes.
The length of a string assigned to a variable can be greater than the length attribute of the 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 bytes. When this occurs, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or the value 'W' is assigned to the SQLWARN1 field of the SQLCA).
When a string is assigned to a fixed-length binary-string 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 hexadecimal zeroes.
When a string of length n is assigned to a varying-length string variable with a maximum length greater than n, the bytes after the nth byte of the variable are undefined.
The following rules apply when the assignment target is a string. When a datetime data type is involved, see Datetime assignments. For the special considerations that apply when a distinct type is involved in an assignment, especially to a variable, see Distinct type assignments.
When a number is assigned to a string data type, it is converted to the target string data type using the rules for a CAST specification. For more information, see CAST specification.
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 or parameter, an error 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.
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 UTF-16 or UCS-2 blanks.22 The pad character is always a blank, even for bit data.
The length of a string assigned to a variable can be greater than the length attribute of the 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, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or 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), an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or 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 UTF-16 or UCS-2 blanks.22 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 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 string column, a string variable, or a date 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 string column, a string variable, or a time 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 string column, a string variable, or a timestamp variable.
When a datetime value is assigned to a 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.
DATE
TIME
TIMESTAMP
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 variables.
Note that usually no attempt is made to access the file server at retrieval time. 23It 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.
A row ID value can only be assigned to a column, parameter, or variable with a row ID data type. For the value of the ROWID column, the column must be defined as GENERATED BY DEFAULT or OVERRIDING SYSTEM VALUE must be specified. A unique constraint is implicitly added to every table that has a ROWID column that guarantees that every ROWID value is unique. The value that is specified for the column must be a valid row ID value that was previously generated by DB2 UDB for z/OS or DB2 UDB for iSeries.
The rules that apply to the assignments of distinct types to variables are different than the rules for all other assignments that involve distinct types.
The assignment of a distinct type to a variable is based on the source data type of the distinct type. Therefore, the value of a distinct type is assignable to a variable only if the source data type of the distinct type is assignable to the variable.
Example: 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
When the statement is executed, the following cast functions are also generated:
AGE (SMALLINT) RETURNS AGE AGE (INTEGER) RETURNS AGE SMALLINT (AGE) RETURNS SMALLINT
Next, assume that column STU_AGE was defined in table STUDENTS with distinct type AGE. Now, 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 variable when:
Any value can be assigned to a distinct type when:
Example: 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 16 shows whether the assignments are valid.
INSERT INTO TABLE1 (Y) SELECT X FROM TABLE2
| TABLE2.X | TABLE1.Y | Valid | Reason |
|---|---|---|---|
| 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 |
When a LOB locator is used, it can refer to any string data. If a LOB locator is used for the first fetch of a cursor and the cursor is on a remote server, LOB locators must be used for all subsequent fetches unless the *NOOPTLOB precompile option is used.
Numbers are compared algebraically; that is, with regard to sign. For example, –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 that 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 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.
When string and numeric data types are compared, the string is converted to the numeric data type and must contain a valid string representation of a number.
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 lengths of the two strings are identical. If the strings are equal up to the length of the shorter string length, the shorter string is considered less than the longer string even when the remaining bytes in the longer string are hexadecimal zeros. Note that binary strings cannot be compared to character strings unless the character string is cast to a binary string.
Character and UTF-16 or 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 UTF-16 or UCS-2 graphic data, the pad character is a UTF-16 blank. 24
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.
In an application that will run in multiple environments, the same sort sequence (which depends on the CCSIDs of the environments) must be used to ensure identical results. The following table illustrates the differences between EBCDIC, ASCII, and the DB2 UDB LUW default sort sequence for United States English by showing a list that is sorted according to each one.
| ASCII and Unicode | EBCDIC | DB2 UDB LUW Default |
|---|---|---|
| 0000 | @@@@ | 0000 |
| 9999 | co-op | 9999 |
| @@@@ | coop | @@@@ |
| COOP | piano forte | co-op |
| PIANO-FORTE | piano-forte | COOP |
| co-op | COOP | coop |
| coop | PIANO-FORTE | piano forte |
| piano forte | 0000 | PIANO-FORTE |
| piano-forte | 9999 | piano-forte |
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, EXCEPT, INTERSECT, 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, any necessary conversion applies to the string as follows:
| First Operand | Second Operand | |||
|---|---|---|---|---|
| SBCS Data | DBCS Data | Mixed Data | UTF-16 or UCS-2 Data | |
| SBCS Data | see below | second | second | second |
| DBCS Data | first | see below | second | second |
| Mixed Data | first | first | see below | second |
| UTF-16 or 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:
| First Operand | Second Operand | ||||
|---|---|---|---|---|---|
| Column Value | Derived Value | Special Register | Constant | Variable | |
| Column Value | second | second | second | second | second |
| Derived Value | first | second | second | second | second |
| Special Register | first | first | second | second | second |
| Constant | first | first | first | second | second |
| Variable | first | first | first | first | second |
A variable that contains 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 is returned if a character of the string cannot be converted or the CCSID Conversion Selection Table (Coded character sets and CCSIDs) 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 DATALINK operand cannot be directly compared to any data type. The DLCOMMENT, DLLINKTYPE, DLURLCOMPLETE, DLURLPATH, DLURLPATHONLY, DLURLSCHEME, and DLURLSERVER scalar functions can be used to extract character string values from a datalink which can then be compared to other strings.
A ROWID operand cannot be directly compared to any data type. To compare the bit representation of a ROWID, first cast the ROWID to a character string.
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 ***INCORRECT*** 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