WHERE clause


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, <= Less than or Equal
GT, > 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
truefalse
falsetrue
unknownunknown

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:
ANDtruefalseunknown
truetruefalseunknown
falsefalsefalsefalse
unknownunknownfalseunknown

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:
ORtruefalseunknown
truetruetruetrue
falsetruefalseunknown
unknowntrueunknownunknown

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

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