NE, <>, ><
LT, <
LE, <=
GT, >
GE, >=
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 g2Effect
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 gAddition
... 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 hEffect
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 itabEffect
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] NULLEffect
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
PerformanceThe 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 condEffect
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 |
| false | true |
| unknown | 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 cond2Effect
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 |
|---|---|---|---|
| true | true | false | unknown |
| false | false | false | false |
| unknown | unknown | 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 cond2Effect
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 |
|---|---|---|---|
| true | true | true | true |
| false | true | false | unknown |
| unknown | true | unknown | 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 < '01000000' OR
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 < '01000000' OR 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
PerformanceSince 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 condEffect
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
PerformanceConditions 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
|
|
 |