ALLBASE/SQL Reference Manual (36216-90216)

280 Chapter9
Search Conditions
Quantified Predicate
IndicatorVariable1 names an indicator variable, an input host
variable whose value determines whether the associated
host variable contains a NULL value:
>= 0
the value is not NULL
< 0
the value is NULL
LocalVariable contains a value in a procedure.
ProcedureParameter contains a value that is passed into or out of a procedure.
? indicates a dynamic parameter in a prepared SQL statement. The value of
the parameter is supplied when the statement is executed.
Description
• If X is the value of Expression , and (a,b, ..., z) represent the result of a SubQuery or
the elements in a
ValueList , and OP is a comparison operator, then the following are
true:
• X OP ANY (a,b,...,z) is equivalent to X OP a OR X OP b OR...OR X
OP z
• X OP ALL (a,b,...,z) is equivalent to X OP a AND X OP b AND...AND
X OP z
• Character strings are compared according to the HP 8-bit ASCII collating sequence for
ASCII data, or the collation rules for the native language of the DBEnvironment for
NLS data. Column data would either be ASCII data or NLS data depending on how the
column was declared upon its creation. Constants will be ASCII data or NLS data
depending on whether the user is using NLS or not. If an ASCII expression is compared
to an NLS expression, the two expressions are compared using the NLS collation rules.
• Refer to Chapter 7 , “Data Types,” for information about the type conversions that
ALLBASE/SQL performs when you compare values of different types.
• If any value of any element in the value list is a NULL value, then that value is not
considered a part of the
ValueList .
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.