HPlogo ALLBASE/SQL Reference Manual: HP 9000 Computer Systems > Chapter 9 Search Conditions

IN Predicate
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

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 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

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)


EXISTS Predicate
  		 


LIKE Predicate  
Feedback to webmaster