[NOT ]
|
predicate | |||||||
(search-condition) | ||||||||
search-condition | AND
|
search-condition | ||||||
OR
|
This document covers the following topics:
A search-condition
can consist
of a simple predicate
or of multiple search-conditions
combined with the Boolean operators AND
, OR
and
NOT
, and parentheses if required to indicate a desired order of
evaluation.
DEFINE DATA LOCAL 01 NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT * INTO NAME, AGE FROM SQL-PERSONNEL WHERE AGE = 32 AND NAME > 'K' END-SELECT ...
A predicate
specifies a
condition that can be "true", "false" or
"unknown".
In a search-condition
,
a predicate
can consist of a simple or
complex comparison operation or other kinds of conditions.
SELECT NAME, AGE INTO VIEW PERS FROM SQL-PERSONNEL WHERE AGE BETWEEN 20 AND 30 0R AGE IN ( 32, 34, 36 ) AND NAME LIKE '%er' ...
Note:
The percent sign (%) may conflict with
Natural terminal commands. If
so, you must define a terminal command control character different from
%
; see Changing the Terminal
Command Control Character in the Terminal
Commands documentation.
The individual predicates are explained in the following topics (for further information on predicates, please refer to the relevant literature). According to the syntax above, they are called as follows:
{scalar-expression comparison scalar-expression} |
A comparison predicate compares two values or a set of values with another set of values.
In the syntax diagram above,
comparison
can be one of the following operators:
=
|
equal to |
<
|
less than |
>
|
greater than |
<=
|
less than or equal to |
>=
|
greater than or equal to |
<>
|
not equal to |
See information on scalar-expression
.
(select-expression) |
A subquery
is a
select-expression
that is nested inside
another such expression.
DEFINE DATA LOCAL 1 #NAME (A20) 1 #PERSNR (I4) END-DEFINE ... SELECT NAME, PERSNR INTO #NAME, #PERSNR FROM SQL-PERSONNEL WHERE PERSNR IN ( SELECT PERSNR FROM SQL-AUTOMOBILES WHERE COLOR = 'black' ) ... END-SELECT
For further information, see Select Expressions.
scalar-expression [NOT ] BETWEEN
scalar-expression AND
scalar-expression
|
A BETWEEN
predicate compares a value with a range of
values.
See information on scalar-expression
.
column-reference [NOT ]
LIKE
atom
|
A LIKE
predicate searches for strings that have a certain
pattern.
See information on
column-reference
and atom
.
column-reference IS [NOT ] NULL
|
A NULL
predicate tests for null values.
See information on
column-reference
.
scalar-expression [NOT ] IN |
subquery | ||
(atom) |
An IN
predicate compares a value or a set of values with a
collection of values.
See information on
scalar-expression
and atom
.
See information on
subquery
.
scalar-expression comparison | ALL
|
subquery | ||
ANY
|
||||
SOME
|
A quantified predicate compares a value or a set of values with a collection of values.
See information on scalar-expression
,
comparison
,
and subquery
.
EXISTS
subquery
|
An EXISTS
predicate tests for the existence of certain
rows.
The EXISTS
predicate evaluates to true only if the result
of evaluating the subquery
is not
empty; that is, if there exists at least one record (row) in the
FROM
table of the subquery
satisfying the search condition of the WHERE
clause of this
subquery
.
Example of EXISTS
:
DEFINE DATA LOCAL 1 #NAME (A20) END-DEFINE ... SELECT NAME INTO #NAME FROM SQL-PERSONNEL WHERE EXISTS ( SELECT * FROM SQL-EMPLOYEES WHERE PERSNR > 1000 AND NAME < 'L' ) ... END-SELECT ...
See information on
subquery
.