HP 3000 Manuals HP 3000 Manuals
IN Predicate
An IN predicate compares an expression with a list of specified values or
a list of values derived from a subquery. The predicate evaluates to
TRUE if the expression is equal to one of the values in the list. If the
NOT option is used, the predicate evaluates to TRUE if the expression is
not equal to any of the values in the list.
Scope
SQL Data Manipulation Statements
SQL Syntax
Expression [NOT] IN {SubQuery }
{(ValueList)}
Parameters
Expression An expression specifies a value to be obtained. The
syntax of expressions is presented in the "Expressions"
chapter. Both numeric and non-numeric expressions are
allowed in quantified predicates. The expression may not
include subqueries or LONG columns.
NOT reverses the value of the predicate that follows it.
SubQuery A subquery is a nested query. The syntax of subqueries
is presented in the description of the SELECT statement
in the "SQL Statements" chapter.
ValueList defines a list of values to be compared against the
expression's value. The syntax for ValueList is:
{USER }
{CurrentFunction }
{ {Integer} }
{[+] {Float } }
{[-] {Decimal} }
{ }
{'CharacterString' }
{0xHexadecimalString } [,...]
{:HostVariable [[INDICATOR] :IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
{LongColumnFunction }
{StringFunction }
USER USER evaluates to the DBEUserID. In
ISQL, it evaluates to the DBEUserID
of the ISQL user. From an
application program, it evaluates
DBEUserID of the individual running
the program. USER behaves like a
CHAR(20) constant, with trailing
blanks if the login name has fewer
than 20 characters.
CurrentFunction indicates the value of the current
DATE, TIME, or DATETIME.
Integer indicates a value of type INTEGER or
SMALLINT.
Float indicates a value of type FLOAT.
Decimal indicates a value of type DECIMAL.
CharacterString specifies a CHAR, VARCHAR, DATE,
TIME, DATETIME, or INTERVAL value.
Whichever is shorter--the string or
the expression value--is padded with
blanks before the comparison is made.
HexadecimalString specifies a BINARY or VARBINARY
value. If the string is shorter than
the target column, it is padded with
binary zeroes; if it is longer than
the target column, the string is
truncated.
HostVariable contains a value in an application
program being input to the
expression.
IndicatorVariable names an indicator variable, whose
value determines whether the
associated host variable contains a
NULL value:
> = 0 the value is not NULL
< 0 the value is NULL (The value
in the host variable will be
ignored.)
? is a place holder for a dynamic
parameter in a prepared SQL statement
in an application program. The value
of the dynamic parameter is supplied
at run time.
LocalVariable contains a value in a procedure.
ProcedureParameter contains a value that is passed into
or out of a procedure.
Built-inVariable is one of the following built-in
variables used for error handling:
* ::sqlcode
* ::sqlerrd2
* ::sqlwarn0
* ::sqlwarn1
* ::sqlwarn2
* ::sqlwarn6
* ::activexact
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 the chapter "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')
If the indicator variable is >= 0 and PartNumber is one of '1123-P-01',
'1733-AD-01', or :PartNumber, then the predicate evaluates to true.
If the indicator variable is < 0, the rows containing the part numbers
1123-P-01 and 1733-AD-01 are selected; but no rows will be selected based
upon the value in :PartNumber.
EXEC SQL SELECT PartNumber
FROM PurchDB.Parts
WHERE PartNumber
IN ('1123-P-01', '1733-AD-01', :PartNumber :PartInd)