ALLBASE/SQL Reference Manual (36216-90216)
270 Chapter9
Search Conditions
IN Predicate
The first six of these have the same meaning that they
have as fields in the SQLCA in application programs. Note
that in procedures, sqlerrd2 returns the number of rows
processed for all host languages. However, in application
programs, sqlerrd3 is used in COBOL, Fortran, and
Pascal, while sqlerr2 is used in C. ::activexact indicates
whether a transaction is in progress or not. For additional
information, refer to the application programming guides
and to Chapter 4 , “Constraints, Procedures, and Rules.”
StringFunction
returns partial values or attributes of character and
binary (including LONG) string data.
LongColumnFunction
returns information from the long column descriptor.
Description
• If X is the value of
Expression
and (a,b, ..., z) represent the result of a
SubQuery
or
the elements in a
ValueList
, then the following are true:
• X IN (a,b,...,z) is equivalent to X = ANY (a,b,...,z)
• X IN (a,b,...,z) is equivalent to X = a OR X = b OR...OR X = z
• X NOT IN (a,b,...,z) is equivalent to NOT (X IN (a,b,...,z))
• Refer to the "Data Types" chapter for information about the type conversions that
ALLBASE/SQL performs when you compare values of different types.
• You can use host variables in the
ValueList
. If an indicator variable is used and
contains a value less than zero, the value in the corresponding host variable is
considered to be unknown.
NOTE
To be consistent with the standard SQL and to support portability of code, it
is strongly recommended that you use a −1 to indicate a NULL value.
However, ALLBASE/SQL interprets all negative indicator variable values as
indicating a NULL value in the corresponding host variable.
• If all values in the
ValueList
are NULL, the predicate evaluates to unknown.
Example
Get part numbers of parts whose weight is 12, 16, or 17.
SELECT P.PNO
FROM P
WHERE P.WEIGHT IN (12, 16, 17)
Get the names of suppliers who supply part number 'P2'.
SELECT S.SNAME
FROM S
WHERE S.SNO IN (SELECT SP.SNO FROM SP
WHERE SP.SNO = 'P2')