Variants
1. … WHERE f op g
2. … WHERE f [NOT] BETWEEN g1 AND g2
3. … WHERE f [NOT] LIKE g
4. … WHERE f [NOT] IN (g1, …, gn)
5. … WHERE f [NOT] IN itab
6. … WHERE f IS [NOT] NULL
7. … WHERE NOT cond
8. … WHERE cond1 AND cond2
9. … WHERE cond1 OR cond2
10. … WHERE (itab)
11. … WHERE cond AND (itab)
12. … FOR ALL ENTRIES IN itab WHERE cond
Effect
If a WHERE clause is specified with the commands SELECT , OPEN CURSOR , UPDATE and DELETE , only the
lines of the database table (or view ) which satisfy the specified condition(s)
are selected.
With Open SQL key words, automatic client handling is normally active. This
ensures that only data from the current client is processed when you are
working with client-specific tables. Therefore, specifying a client in the
WHERE clause does not make sense and is rejected as an error by the syntax
check.
If you use the addition … CLIENT SPECIFIED in the FROM
clause to switch off automatic client handling, the client field is treated
like a normal table field and you can formulate conditions for it in the WHERE
clause.
Notes
If, when using transparent tables, there are frequent
accesses without a complete primary key or the data is sorted in an order other
than by the primary key, you should consider whether it is worth creating an
index .
If no WHERE condition is specified, all lines (in the current client) are
selected.
Variant 1
…WHERE f op g
Effect
The condition is true if the comparison f op g is true. The
condition is false if the comparison f op g is false. Here, f is the name of a
database field (without a prefix) and g is the name of any field or literal.
You can use any of the following comparison operators:
, = EQual
NE, , >< Not Equal
LT, < Less Than
LE, Greater Than
GE, >= Greater than or Equal
Examples
Select all Lufthansa flight connections:
… WHERE CARRID = ‘LH’
Select passenger planes with fewer than 200 seats:
… WHERE SEATSMAX LT 200
Notes
If the database field f contains the NULL value, the result
of evaluating the condition is neither “true” nor “false”,
but “unknown”.
You can reverse the effect of a comparison operator by prefixing it with NOT ,
i.e. NOT EQ corresponds to NE , while NOT LE corresponds to GT , etc.
Example
If a line contains the NULL value for the field TELEPHONE ,
you cannot use any of the following conditions to select this line:
… WHERE TELEPHONE = ‘ ‘
… WHERE TELEPHONE ‘ ‘
… WHERE NOT TELEPHONE = ‘ ‘
Variant 2
… WHERE f [NOT] BETWEEN g1 AND g2
Effect
The condition is true, if the contents of the table field f
(do not) lie between g1 and g2 . Otherwise, the condition is false.
Examples
Select all passenger planes with between 200 and 250 seats:
… WHERE SEATSMAX BETWEEN 200 AND 250
Note
If the database field f contains the NULL value, the result
of evaluating the condition is neither “true” nor “false”,
but “unknown”.
Variant 3
… WHERE f [NOT] LIKE g
Addition
… ESCAPE h
Effect
The condition is true, if the contents of the table field f
(do not) correspond to the contents of the field g . Within the search pattern,
two characters have a particular meaning:
‘_’
stands for any one character.
‘%’
stands for any character string, including a blank string.
If the statement does not apply, the condition is false.
Examples
Select all customers whose names begin with ‘M’ :
… WHERE NAME LIKE ‘M%’
Select all texts which contain the word ‘customer’ :
… WHERE TEXT LIKE ‘%customer%’
Select all customers whose names do not contain ‘n’ as the second letter:
… WHERE NAME NOT LIKE ‘_n%’
Notes
You can
apply LIKE only to alphanumeric database fields, i.e. the table field f must be
one of the Dictionary types ACCP , CHAR , CLNT , CUKY , LCHR , NUMC , UNIT ,
VARC , TIMS or DATS . The comparison field g must always be type C .
The pattern can consist of up to 2n – 1 characters, if n is the same length as
the field f .
Trailing blanks in the comparison field g are ignored. If a pattern
contains trailing blanks, you must enclose it in quotation marks. If a
quotation mark is part of the pattern, you must double the opening and closing
quotation marks.
If the database field f contains the NULL value, the result of evaluating the
condition is neither “true” nor “false”, but
“unknown”.
Addition
… ESCAPE h
Effect
The field h contains an escape symbol. Within the pattern g
, this makes a special character following the escape symbol lose its special
meaning.
Example
Select all function modules whose names begin with ‘EDIT_’ :
… WHERE FUNCNAME LIKE ‘EDIT#_%’ ESCAPE ‘#’
Notes
An escape symbol can only precede one of the special
characters ‘%’ and ‘_’ or itself.
The addition ESCAPE g refers only to the immediately preceding LIKE condition.
If a WHERE clause contains several LIKE conditions, you must specify ESCAPE as
many times as required.
The field g which contains the escape symbol is always treated like a type C
field of length 1.
The addition ESCAPE g is not supported with pooled and cluster tables.
Variant 4
… WHERE f
[NOT] IN (g1, …, gn)
Effect
The condition is true, if the contents of the table field f
are (not) the same as the contents of one of the fields or literals g1, …, gn
. Otherwise, the condition is false.
Examples
Select the flight connections of American Airlines,
Lufthansa and Singapore Airlines:
… WHERE CARRID IN (’AA’, ‘LH’, ‘SQ’)
Select all flight connections apart from those of Lufthansa and Lauda Air:
… WHERE CARRID NOT IN (’LH’, ‘NG’)
Notes
There must be no blanks between the opening parenthesis
which introduces the field list and the name g1 of the first field in the field
list.
If the database field f contains the NULL value, the result of evaluating the
condition is neither “true” or “false”, but
“unknown”.
Variant 5
… WHERE [NOT] in itab
Effect
The condition is true, if the contents of the database table
field f are (not) found in the internal table itab . Otherwise, the condition
is false.
The internal table itab must have the structure of a RANGES
table for f . You can define it with RANGES itab FOR f , SELECT-OPTIONS itab
FOR f or DATA . If itab is defined with SELECT-OPTIONS , it is automatically filled with the
user’s predefined values. Otherwise, you must specify it explicitly in the
program. This is a method of specifying parts of the WHERE condition at runtime.
Each line of itab contains an elementary condition where the columns have the
following meaning:
SIGN Specifies whether the condition is inclusive or exclusive. Possible values
are:
I Inclusive
E Exclusive
OPTION Contains the operator for the elementary condition. Possible values are:
EQ, NE EQual, Not Equal
BT, NB BeTween, Not Between
CP, NP Contains Pattern,
does Not contain Pattern
LT, LE Less Than, Less than or Equal
GT, GE Greater Than, Greater than or Equal
LOW With EQ , NE , LT , LE , GT and GE , this field contains the compare value.
With BT and NB , it contains the lower limit of a range. With CP and NP , it
can extend beyond LOW and HIGH .
HIGH With BT and NB , this field contains the upper limit of a range. With CP
and NP , it contains the end of the specification begun in LOW .
The elementary conditions in itab are combined together to form a complex
condition in the following manner:
If
itab is empty, the condition f IN itab is always true.
If
itab contains only the inclusive elementary conditions i1, …, in , the
resulting condition is
( i1 OR … OR in )
If
itab contains only the exclusive elementary conditions e1, …, em , the
resulting condition is
( NOT e1 ) AND … AND ( NOT em )
If
itab contains the inclusive elementary conditions i1, …, in and the
exclusive elementary conditions e1, …, em , the resulting condition is
( i1 OR … OR in ) AND
( NOT e1 ) AND … AND ( NOT em )
Example
Select the customer numbers
‘10000000′
to ‘19999999′,
‘01104711′
as well as
all
customer numbers greater than or equal to ‘90000000′,
but not the customer numbers
‘10000810′
to 10000815′,
‘10000911
as well as
all
customer numbers where the fifth character is a ‘5′.
TABLES: SCUSTOM.
SELECT-OPTIONS: R FOR SCUSTOM-ID.
* RANGES: R FOR SCUSTOM-ID.
* Let R be filled as follows (the order of lines is
* of no significance):
*
* SIGN OPTION LOW HIGH
* I EQ 01104711
* I BT 10000000 19999999
* I GE 90000000
* E EQ 10000911
* E BT 10000810 10000815
* E CP ++++5*
*
* This generates the condition
*
* ( ID = ‘01104711′ OR
* ID BETWEEN ‘10000000′ AND ‘19999999′ OR
* ID >= ‘90000000′ ) AND
* ID ‘10000911′ AND
* ID NOT BETWEEN ‘10000810′ AND ‘10000815′ AND
* ID NOT LIKE ‘____5%’
*
SELECT * FROM SCUSTOM WHERE ID IN R.
…
ENDSELECT.
Notes
Since a condition of the form f IN itab triggers a complex
condition at runtime, but the size of the SQL statement is restricted by the
underlying database system (e.g. no more than 8 KB), the internal table itab
must not contain too many lines.
If the database field f contains the NULL values, the result of evaluating the
condition is neither “true” nor “false”, but
“unknown”.
Variant 6
… WHERE f IS [NOT] NULL
Effect
The condition is true if the contents of the table field f
(do not) contain the NULL value.
Example
Select all customers with customer numbers for which no
telephone number is specified:
… WHERE TELEPHONE IS NULL
Note
Performance
The SAP buffer does not support this variant. Therefore, the effect of each SELECT command on a buffered table or on a view of fields
from buffered tables that contains … WHERE f IS [NOT] NULL is as if the
addition BYPASSING BUFFER was specified in the FROM
clause.
Variant 7
… WHERE NOT cond
Effect
NOT cond is true if cond is false. The condition is false of
cond is true. This produces the following truth table:
NOT
true
false
unknown
cond can be any condition according to the WHERE variants 1 – 9. NOT takes priority
over AND and OR . You can also determine the evaluation sequence by using
parentheses.
Note
Parentheses which determine the evaluation sequence must be
preceded and followed by a blank.
Example
Select the customers with customer numbers who do not live
in postal code area 68:
… WHERE NOT POSTCODE LIKE ‘68%’
Variant 8
… WHERE cond1 AND cond2
Effect
cond1 AND cond2 is true if cond1 and cond2 are true. The
condition is false if cond1 or cond2 is false. This produces the following
truth table:
AND
true
false
unknown
cond1 and cond2 can be any conditions according to the WHERE variants 1 – 9. AND
takes priority over OR , but NOT takes priority over AND . You can also
determine the evaluation sequence by using prenetheses.
Note
Parentheses which determine the evaluation sequence must be
preceded and followed by a blank.
Example
Select the customers with customer numbers which are less
than ‘01000000′ and do not live in the postal code area 68.
… WHERE ID < ‘01000000′
AND NOT
POSTCODE LIKE ‘68%’
Variant 9
… WHERE cond1 OR cond2
Effect
cond1 OR cond2 is true if cond1 or cond2 is true. The
condition is false if cond1 and cond2 are false. This produces the following
truth table:
OR
true
false
unknown
cond1 and cond2 can be any conditions according to the WHERE variants 1 – 9.
Both NOT and AND take priority over OR . You can also determine the evaluation
sequence by using parentheses.
Note
Parentheses which determine the evalutation sequence must be
preceded and followed by a blank.
Example
Select the customers with customer numbers which are less
than ‘01000000′ or greater than ‘02000000′:
… WHERE ID ‘02000000′.
Select the customers with customer numbers which are less than ‘01000000′ or
greater than ‘02000000′ and do not live in the postal code areas 68 or 69
… WHERE ( ID ‘02000000′ )
AND NOT
( POSTCODE LIKE ‘68%’ OR POSTCODE LIKE ‘69%’ )
Variant 10
… WHERE (itab)
Effect
The condition is true if the contents of the table fields
satisfy the condition stored in the internal table itab . itab is filled at
runtime, i.e. the condition for the fields is specified dynamically.
Notes
This variant is exclusively for use with SELECT . The
internal table itab can only have one field which must be of type C and not be
greater than 72 characters. itab must be specified in parentheses with no
blanks between the parentheses and the table name. The condition specified in
the internal table itab must have the same form as a condition in the ABAP/4
source code. The following restrictions apply:
- You can only use literals as values, not variables.
- The operator IN cannot be used in the form f1 IN itab1 .
The internal table itab can be empty.
Note
Performance
Since the syntax check may not be performed until runtime, a WHERE condition
needs more execution time than a corresponding specification in the program
code.
Example
Display flight connections after entry of airline carrier
and flight number:
TABLES: SPFLI.
PARAMETERS: CARR_ID LIKE SPFLI-CARRID,
CONN_ID LIKE SPFLI-CONNID.
DATA: WTAB(72) OCCURS 100 WITH HEADER LINE,
AND(3).
REFRESH WTAB.
IF NOT CARR_ID IS INITIAL.
CONCATENATE ‘CARRID = ”’ CARR_ID ”” INTO WTAB.
APPEND WTAB.
AND = ‘AND’.
ENDIF.
IF NOT CONN_ID IS INITIAL.
CONCATENATE AND ‘ CONNID = ”’ CONN_ID ”” INTO WTAB.
APPEND WTAB.
ENDIF.
SELECT * FROM SPFLI WHERE (WTAB).
WRITE: / SPFLI-CARRID, SPFLI-CONNID, SPFLI-CITYFROM,
SPFLI-CITYTO, SPFLI-DEPTIME.
ENDSELECT.
Variant 11
… WHERE cond AND (itab)
Effect
Like variant 10. For the condition to be true, the table
fields must also satisfy the condition cond .
Note
When specifying a condition cond in the program code
together with a condition in an internal table itab , the table name must
appear in parentheses after the condition cond and be linked with AND . There
must be no blanks between the name of the internal table and the parentheses.
Variant 12
… FOR ALL ENTRIES IN itab WHERE cond
Effect
Selects only those lines of the database table which satisfy
the WHERE condition cond where each occurring replacement symbol itab-f is
replaced by the value of the component f in the internal table itab for at
least one line. Clearly, a SELECT command with … FOR ALL ENTRIES IN itab
WHERE cond forms the union of solution sets for all SELECT commands which
result when, for each line of the internal table itab , each symbol itab-f
addressed in the WHERE condition is replaced by the relevant value of the
component f in this table line. Duplicate lines are eliminated from the result
set. If the internal table itab contains no entries, the processing continues
as if the WHERE condition cond has failed.
Example
Display a full list of flights on 28.02.1995:
TABLES SFLIGHT.
DATA: BEGIN OF FTAB OCCURS 10,
CARRID LIKE SFLIGHT-CARRID,
CONNID LIKE SFLIGHT-CONNID,
END OF FTAB,
RATIO TYPE F.
* Let FTAB be filled as follows:
*
* CARRID CONNID
* ————–
* LH 2415
* SQ 0026
* LH 0400
SELECT * FROM SFLIGHT FOR ALL ENTRIES IN FTAB
WHERE CARRID = FTAB-CARRID AND
CONNID = FTAB-CONNID AND
FLDATE = ‘19950228′.
RATIO = SFLIGHT-SEATSOCC / SFLIGHT-SEATSMAX.
WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, RATIO.
ENDSELECT.
Notes
… FOR ALL ENTRIES IN itab WHERE cond can only be used with
a SELECT command.
In the WHERE condition … FOR ALL ENTRIES IN itab WHERE cond , the symbol
itab-f always has the meaning of a replacement symbol and must not be confused
with the component f of the header line in the internal table itab . The internal
table itab does not have to have a header line.
The line structure of the internal table itab must be a field string. Each
component of this field string which occurs in a replacement symbol in the
WHERE condition must be of exactly the same type and length as the
corresponding component in the table work area (see TABLES
).
Replacement symbols must not occur in comparisons with the operators LIKE ,
BETWEEN and IN .
FOR ALL ENTRIES IN itab excludes ORDER BY f1 … fn in the ORDER-BY clause .
The internal table itab cannot be used at the same time in the INTO clause .
Notes
Performance
Conditions should always be checked with the WHERE clause, not with CHECK , because
the data can then be selected with an index. Also, this reduces the load on the
network.
For frequently used SELECT statements, you should employ an index. In the WHERE
clause, the fields of the index should be specified in the defined order and linked
by the logical AND with comparisons for equality.
Complex WHERE clauses are unsuitable for the statement optimizer of a database
system because they must be broken down into several single statements.
In a WHERE clause, the logical NOT cannot be supported by an index.
Index
© SAP AG 1996
