IN Predicate

Scope

SQL Syntax

  Expression [NOT] IN { SubQuery
                        (ValueList) }

Parameters

Expression

An expression specifies a value to be obtained. The syntax of expressions is presented in Chapter 8 "Expressions" 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 Chapter 12 "SQL Statements S - Z"

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'
    OxHexadecimalString
    :HostVariable [[INDICATOR]:IndicatorVariable]
    ?
    :Local Variable
    :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 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

   SELECT P.PNO
     FROM P
    WHERE P.WEIGHT IN (12, 16, 17)

   SELECT S.SNAME
     FROM S
    WHERE S.SNO IN  (SELECT SP.SNO FROM SP
                     WHERE SP.SNO = 'P2')

   EXEC SQL SELECT PartNumber
              FROM PurchDB.Parts
             WHERE PartNumber
                IN ('1123-P-01', '1733-AD-01', :PartNumber :PartInd)