ALLBASE/SQL Reference Manual (36216-90216)

266 Chapter9
Search Conditions
Comparison Predicate
the two expressions are compared using the NLS collation rules. The case insensitive
NLS comparison is done by using the NLSCANMOVE and NLSCOLLATE intrinsics. The same
ASCII characters in upper and lower case are equivalent. Accent characters (extended
character) in upper and lower case are also equivalent. However, an accent character
may not be the same as its ASCII equivalent, depending on the specific language
collation table.
Extended upper and lower case characters are not equivalent to the ASCII expression.
They are compared to the NLS collation table.
If a case sensitive character column is compared to a character column that is not case
sensitive, both columns are treated as case sensitive. If a string constant is compared to
a column that is not case sensitive, then the string constant is treated as not case
sensitive.
• Refer to Chapter 7 , “Data Types,” for type conversion that ALLBASE/SQL performs
when you compare values of different types.
• For purposes of the Comparison Predicate, a NULL value on either or both sides of the
predicate causes it to evaluate to unknown. Thus, two NULL values on either side of an
equals predicate will not result in a TRUE result but rather in unknown.
• A NULL value in an expression causes comparison operators to evaluate to unknown.
Refer to the "Search Condition" section at the beginning of this chapter for more
information on evaluation of operators.
• A subquery must return a single value (one column of one row). If the subquery returns
more than one value, an error is given. If the subquery returns no rows, the predicate
evaluates to unknown.
Example
The part numbers of parts that require fewer than 20 days for delivery are retrieved.
SELECT PartNumber
FROM PurchDB.SupplyPrice
WHERE DeliveryDays < 20