One of the most common operations in SQL is to search through a table, choosing certain rows for processing. A search condition is the criterion for choosing rows.
In the following select-statement example, CODE = 'A' AND PART='B' AND TYPE='X' constitute the search condition:
SELECT * FROM T1 WHERE CODE = 'A' AND PART='B' AND TYPE='X'
When you are constructing search conditions, be careful to perform arithmetical operations only on numeric data types, and to make comparisons only among compatible data types. Graphic data types are compatible only with other graphic data types. If you use a host variable in an expression, its host language data type must be compatible with the rest of the expression.
Whenever an arithmetic or comparison operator has operands of two different types, the database manager evaluates it in the greater of the two types: FLOAT takes precedence over DECIMAL, which takes precedence over INTEGER, which takes precedence over SMALLINT. For example, if the PRICE column is of type INTEGER and has the value 25, the expression PRICE*.5 will evaluate to 12.5, a decimal value. The predicate PRICE*.5=12 is false, because the decimal value forces the predicate to be evaluated in decimal. (Decimal values are stored in System/390(TM) packed decimal format.)
The system computes all floating-point values in normalized form, as described in the ESA/390 Principles of Operation manual. When a floating-point value is stored in a table, it may not be stored exactly as entered. For example, an SQL INSERT statement could specifically insert the constant 3E0 into a column. Internally, however, the value might actually be stored as 2.9999. Floating-point values may become even more imprecise when arithmetic operations are performed on them. You should use the BETWEEN predicate (described later) when comparing floating-point values.
If the operands of an arithmetic or comparison operator are both single-precision and double-precision floating-point data, the former is converted to the latter before any comparison is made or any arithmetic operation performed. If the equals (=) comparison operator compares these two types of data, the result of the comparison may not be what you expected. In the following examples, column C1 is defined to contain single-precision floating-point data and column C2 is defined to contain double-precision floating-point data:
INSERT INTO T1 (C1, C2) VALUES (10.95, 10.95) SELECT * FROM T1 WHERE C1 = 10.95 SELECT * FROM T1 WHERE C2 = 10.95 SELECT * FROM T1 WHERE C1 = C2
The first and second select statements here will return rows that contain the value 10.95. The third select will not return any rows. This is because the 10.95 cannot be exactly expressed as a floating-point value. The double-precision floating-point representation has more significant bits than the single-precision floating-point representation. When the single-precision floating-point value is converted to double-precision float, X'00's are added to the last four bytes of the double-precision equivalent. The single-precision float data is therefore not equal to the double-precision float data and hence the search condition in the last select above is not satisfied.
Decimal numbers have a maximum precision of up to 31 digits. In contrast, a double-precision floating point number preserves up to approximately 17 digits. So when a decimal number with precision greater than 17 is promoted to a floating-point number, digits are lost. Because floating-point numbers can have a larger magnitude than decimal numbers, the float data type is higher than the decimal in the data type promotion scheme. The following example shows how this can cause unexpected results:
SELECT * FROM DEPARTMENT WHERE 1E0 + 12345678901234567890.1
= 12345678901234567890.1;
You would expect this statement to return no rows, because adding one to a constant makes it unequal to itself. To execute this statement, the system promotes the two decimal numbers to floating-point values. When this is done, all but the first 17 digits are lost. When '1E0' is added to the first decimal number, > it is not large enough to change the converted decimal value. The end result is that both sides of the expression evaluate as being equal. It is therefore important to be careful when combining floating-point and decimal data types in expressions.
Arithmetic operations between two items of type SMALLINT produce a result of type INTEGER, in order to avoid possible overflow problems (as might easily occur in multiplication). When INTEGER or SMALLINT values are used in a division computation, the result is of type INTEGER, and any remainder is dropped. (See Converting Data for conversion information.)
The system allows nulls in values in a table. A null is a nonexistent value; that is, it represents a value that is undefined. You can think of a null value as an empty space, or as a space reserved for later insertion of data.
When null values occur within expressions, the value of the expression is also null. For example, in the following predicate both SALARY and COMM may be a null value:
If either SALARY or COMM is null, expression1 above is null.
A search condition is a collection of one or more predicates. Each predicate specifies a test that is applied to the rows of the table. You can connect predicates with the logical operators AND and OR.
predicate1 AND predicate2 OR predicate3
The keyword NOT can be used to negate a predicate:
predicate1 AND NOT predicate2
The precedence rule among the keywords is as follows:
Use parentheses to override this precedence rule if necessary. For example, the search condition in Figure 24 contains three predicates; it is used to find the rows of the EMPLOYEE table pertaining to an employee from department D11 who also has 17 or 18 years of education.
Figure 24. Breakdown of Search Conditions and Predicates
 |
Figure 24 also shows that the format of a predicate is a comparison between two values or expressions. This format is represented as follows:
expression comparison-operator expression
A comparison-operator may be any of the following:
= "equal to" ¬= "not equal to" <> "not equal to" > "greater than" >= "greater than or equal to" < "less than" <= "less than or equal to"
The above symbols are the only comparison operators that you can use in SQL statements. For example, the system does not recognize <> even if it is supported in the host language. The correct representation of inequality is ¬= or <>.
For a detailed description of search conditions, see the DB2 Server for VSE & VM SQL Reference manual.
The following rules apply when the system evaluates predicates:
'A' < 'B' 'A' < 'ABLE' 'Z' < '35' 'A1' < 'B' 'a' < 'A'
'SMITH '.
Trailing blanks are significant in the LIKE predicate; see the DB2 Server for VSE & VM SQL Reference manual.
In addition to the basic predicates that compare two expressions, the system provides the predicates listed below, which you can use either alone or with other predicates by including the keywords AND, OR, and NOT to form a search condition. For detailed information on the rules and use of these predicates, see the DB2 Server for VSE & VM SQL Reference manual.