 |
» |
|
|
|
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 the "Expressions" chapter.
- =
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] ? :LocalVariable :ProcedureParameter ::Built-inVariable LongColumnFunction StringFunction } [,...] - USER
USER evaluates to
logon name. In ISQL, it evaluates to the
logon 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
logon 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 the "Data Types" chapter for information about the type
conversions that ALLBASE/SQL performs when you compare values of
different types.
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')
|
|
