Quantified Predicate

A quantified 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 related to the value list as specified by the comparison operator and the quantifier.

Scope

SQL Data Manipulation Statements

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'
  OxHexadecimalString
  :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

Get supplier numbers for suppliers who supply at least one part in a quantity greater than every quantity in which supplier S1 supplies a part.

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

An alternative, possibly faster form of the query is:

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

Quantified Predicate

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description

Example