SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-91
IN Predicate
constructor is a single value. The data types of the values must be comparable.
The limit for the number of expressions in thein-value-list is 1900.
Considerations for IN
Logical Equivalent Using ANY (or SOME)
The predicate expr IN (expr1, expr2, ... ) is true if and only if the following
predicate is true:
expr = ANY (expr1, expr2, ... )
IN Predicate Results
The IN predicate is true if and only if either of these is true:
•
The result of the row-value-constructor (a row or sequence of values) is
equal to any row of column values specified by table-subquery.
Note that a table subquery is a query expression and can be specified as a form of
a simple table; for example, as the VALUES keyword followed by a list of row
values. See SELECT Statement on page 2-174.
•
The result of the row-value-constructor (a single value) is equal to any of the
values specified by the list of expressions in-value-list.
In this case, it is helpful to think of the list of expressions as a one-column table—a
special case of a table subquery. The degree of the row value constructor and the
degree of the list of expressions are both one.
Comparing Character Data
Two strings are equal if all characters in the same ordinal position are equal.
Lowercase and uppercase letters are not considered equivalent. For comparisons
between character strings of different lengths, the shorter string is padded on the right
with spaces (HEX 20) until it is the length of the longer string. Both fixed-length and
varying-length strings are padded in this way.
For example, NonStop SQL/MX considers the string ‘JOE’ equal to a value JOE stored
in a column of data type CHAR or VARCHAR of width three or more. Similarly,
NonStop SQL/MX considers a value JOE stored in any column of the CHAR data type
equal to the value JOE stored in any column of the VARCHAR data type.
Comparing Numeric Data
Before evaluation, all numeric values in an expression are first converted to the
maximum precision needed anywhere in the expression.
Comparing Interval Data
For comparisons of INTERVAL values, NonStop SQL/MX first converts the intervals to
a common unit. If no common unit exists, NonStop SQL/MX reports an error. Two
INTERVAL values must be both year-month intervals or both day-time intervals.