Quantified Predicate

Scope

SQL Syntax

  Expression { =
               <>
               >
               >=
               <
               <= } { ALL
                      ANY
                      SOME } { SubQuery
                               (ValueList) }

Parameters

Expression

An expression specifies a value to be obtained. The syntax of expressions is presented in Chapter 8 "Expressions"

=

is equal to.

<>

is not equal to.

>

is greater than.

>=

is greater than or equal to.

<

is less than.

<=

is less than or equal to.

ALL, ANY, SOME

are quantifiers which indicate how many of the values from the ValueList or SubQuery must relate to the expression as indicated by the comparison operator in order for the predicate to be true. Each quantifier is explained below:

ALL

the predicate is true if all the values in the ValueList or returned by the SubQuery relate to the expression as indicated by the comparison operator.

ANY

the predicate is true if any of the values in the ValueList or returned by the SubQuery relate to the expression as indicated by the comparison operator.

SOME

a synonym for ANY.

SubQuery

A subquery is a nested query. Subqueries are presented fully in the description of the SELECT statement.

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]
    ?
    :Local Variable
    :ProcedureParameter
    ::Built-inVariable
    LongColumnFunction
    StringFunction      } [, ...]

USER

USER evaluates to login name. In ISQL, it evaluates to the login name of the ISQL user. From an application program, it evaluates to the login name 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, it is truncated.

HostVariable

identifies the host variable containing the column value.

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.

Example

   SELECT DISTINCT SP.SNO
     FROM SP
    WHERE SP.QTY > ALL ( SELECT SP.QTY
                           FROM SP
                          WHERE SP.SNO = 'S1')

   SELECT DISTINCT SP.SNO
     FROM SP
    WHERE SP.QTY > (SELECT MAX(SP.QTY)
                      FROM SP
                     WHERE SP.SNO = 'S1')