HP 3000 Manuals HP 3000 Manuals
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
{= }
{<>} {ALL }
Expression {> } {ANY } {SubQuery }
{>=} {SOME} {(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] :Indicator]}
{? }
{: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.
* 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')