Logical Conditions in the WHERE Clause

Variants:

1. f op g
   

2. f [NOT] BETWEEN g1 AND g2
   

3. f [NOT] LIKE g
   

4. f [NOT] IN (g1, ..., gn)
   

5. f [NOT] IN subquery
   

6. f [NOT] IN itab
   

7. f IS [NOT] NULL
   

8. [NOT] EXISTS subquery
   

9. NOT cond

10. cond1 AND cond2

11. cond1 OR cond2

12. (source_text)

Effect

Specified in the

• WHERE and HAVING clauses, and the ON addition to the FROM CLAUSE, of the SELECT and OPEN CURSOR statements,
  
• WHERE clause of the UPDATE command and the
  
• WHERE clause of the DELETE command,
  

to check whether data from various database tables or views meets certain criteria.

In some cases, the syntax rules that apply to Unicode programs are different than those for non-Unicode programs. See Open SQL and Unicode.

Note

You must not use database columns of the types STRING or RAWSTRING in logical conditions.

Variant 1

f op g

Effect

The condition is fulfilled for a table line if the statement "f op g" is true for the values of f and g. f must always be a field descriptor and g either a field descriptor, an ABAP field or a subquery. A HAVING clause may contain f and g aggregate expressions. You can use any of the following relational operators:

EQ, =           (Equal)
NE, <>            (Not Equal)
LT, <             (Less Than)
LE, <=            (Less or Equal)
GT, >             (Greater Than)
GE, >=            (Greater or Equal)

The syntax check performed in an ABAP Objects context is stricter than in other ABAP areas. See Wrong logical operators in the WHERE clause.

Examples

Example to select all Lufthansa flights:

DATA WA_SPFLI LIKE SPFLI

SELECT * FROM SPFLI INTO WA_SPFLI
       WHERE CARRID = 'LH'.
  WRITE: / WA_SPFLI-CITYFROM, WA_SPFLI-CITYTO.
ENDSELECT.

To select all flights that are not fully booked:

DATA WA_SFLIGHT TYPE SFLIGHT

SELECT * FROM SFLIGHT INTO WA_SFLIGHT
       WHERE SFLIGHT-SEATSOCC LT SFLIGHT-SEATSMAX.
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, WA_SFLIGHT-FLDATE.
ENDSELECT.

To select the flights with the greatest number of passengers:

DATA WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT
       WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, WA_SFLIGHT-FLDATE.
ENDSELECT.

If the statement "f op g" is not true, it is not automatically false: It is only false, if neither f nor g has the value NULL. If f or g is ZERO, the check returns the value unknown for the statment "f op g". See also variant 7.

Example

None of the following commands contains a line in which the field TELEPHONE contains the value NULL:

DATA WA_SCUSTOM TYPE SCUSTOM

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE = ' '.
ENDSELECT.
SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE <> ' '.
ENDSELECT.
SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE NOT TELEPHONE = ' '.
ENDSELECT.

Notes

1. If f and g are field identifiers, they must have the same type and length. Otherwise, the results depend on the database system in use.
   
   
   
2. If g is an ABAP field, its value is converted if necessary into the type of the field identifier f after the MOVE statement.
   
   
   
3. You can use the LT, LE, GT and GE operators to compare values with any database fields. However, note that the sequence of characters can vary from codepage to codepage.
   
   
   

Variant 2

f [NOT] BETWEEN g1 AND g2

Effect

The condition is met for a table entry if the statement "f is (not) between g1 and g2" is true for the values of f, g1 and g2. f must always be a field descriptor and g1 and g2 ABAP fields. If f has the value NULL, the check for the statement returns unknown.

Example

Example to select all passenger aircraft with 200 - 250 seats.

DATA WA_SAPLANE TYPE SAPLANE.
DATA:  LOW  TYPE I VALUE 200,
       HIGH TYPE I VALUE 250.

SELECT * FROM SAPLANE INTO WA_SAPLANE
       WHERE SEATSMAX BETWEEN LOW AND HIGH.
  WRITE: / WA_SAPLANE-PLANETYPE.
ENDSELECT.

Note

You cannot use this variant in the ON addition of the FROM clause.

Variant 3

f [NOT] LIKE g

Addition:

... ESCAPE h

Effect

The condition is met for a table entry if the statement "f (does not) equal the pattern in g" is true for the values of f and g. f must always be a field descriptor, and g an ABAP field or an ABAP string. If f has the value NULL, then the result of the check for the statement is unknown. Within a pattern, there are two special characters:

• '_' (underscore) stands for any single character.
  
• '%' (percentage sign) stands for any sequence of characters, including an empty string.
  
  
  

Examples

Example to select all customers whose name begins with 'M':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
       INTO CORRESPONDING FIELDS OF SCUSTOM_WA
       WHERE NAME LIKE 'M%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Example to select all customers whose name contains 'huber':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
       INTO CORRESPONDING FIELDS OF SCUSTOM_WA
       WHERE NAME LIKE '%huber%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Example to select all customers whose name does not contain 'n' as the second character:

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
       INTO CORRESPONDING FIELDS OF SCUSTOM_WA
       WHERE NAME NOT LIKE '_n%'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Notes

1. LIKE can only be used for alphanumeric database fields. In other words, table field f must have Dictionary type ACCP, CHAR, CLNT, CUKY, LCHR, NUMC, UNIT, VARC, TIMS or DATS. The comparison field g must always have type C.
   
   
   
2. The maximum length of the pattern is 2n - 1 characters, where n is the length of field f.
   
   
   
3. Trailing spaces are ignored in comparison field g. If a pattern contains trailing spaces, you must enclose it in single inverted commas ('). If your pattern is enclosed in inverted commas and you also want to include inverted commas as part of the pattern, the inverted commas in the pattern must be doubled.
   
   
   
4. You cannot use this variant in the ON addition to the FROM clause.
   
   
   

Addition

... ESCAPE h

Effect

The field h contains an escape symbol. A special character within the pattern g is not regarded as a special character if it is preceded by the escape character.

Example

Example to select all customers whose name begins with '100%':

DATA SCUSTOM_WA TYPE SCUSTOM.

SELECT ID NAME FROM SCUSTOM
    INTO CORRESPONDING FIELDS OF SCUSTOM_WA
    WHERE NAME LIKE '100#%' ESCAPE '#'.
  WRITE: / SCUSTOM_WA-ID, SCUSTOM_WA-NAME.
ENDSELECT.

Notes

1. You may only place an escape symmbol before a special character ('%' and '_') or before itself.
   
   
   
2. The ESCAPE g addition refers to the immediately preceding LIKE condition. If a WHERE clause contains more than one LIKE condition, you must specify ESCAPE more than once if necessary.
   
   
   
3. The field g, which contains the escape symbol, is always regarded as having type C and length 1.
   
   
   
4. You cannot use the ESCAPE g addition with pool or cluster tables.
   
   
   

Variant 4

f [NOT] IN (g1, ..., gn)

Effect

The condition is met for a table entry if the statement "f is (not) contained in the list g1, ..., gn" is true for the values of f, g1, ..., gn. f must always be a field descriptor and g1, ..., gn an ABAP field. If f has the value NULL, the check for the statement returns unknown.

Example

Example to select all destinations served by American Airlines, Lufthansa or Singapore Airlines from Frankfurt:

DATA   TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO
       INTO TARGET        FROM SPFLI
       WHERE
         CARRID   IN ('AA', 'LH', 'SQ') AND
         CITYFROM = 'FRANKFURT'.
  WRITE: / TARGET.
ENDSELECT.

Note

The opening parenthesis that introduces the list, and the name g1 of the first field in the field list must not be separated by a space.

Variant 5

f [NOT] IN subquery

Effect

The condition is met for a table entry if the statement "f is (not) contained in the result of subquery subquery" is true for the value of f. f must always be a field descriptor and subquery a scalar subquery. If f has the value NULL, the result of the check for the statement is unknown.

Example

Example to select flights with the most passengers:

DATA WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT INTO WA_SFLIGHT
       WHERE SEATSOCC IN ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, WA_SFLIGHT-FLDATE.
ENDSELECT.

Note

You cannot use this variant in the ON addition of the FROM clause.

Variant 6

f [NOT] IN itab

Effect

The condition is true if the contents of field f in the database table (do not) satisfy the condition stored in the internal table itab. If the statement is not true, it is false.

The internal table itab must have the structure of a RANGES tablel for f. You can create it using RANGES itab FOR f , SELECT-OPTIONS itab FOR f or DATA. If you create itab using SELECT-OPTIONS, it is automatically filled with the user defaults. Otherwise, you must fill it explicitly in your program. This is a way 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:

I Inclusive
E Exclusive

OPTION

Contains the operator for the elementary condition. Possible values:

EQ, NE EQual, Not Equal
BT, NB BeTween, Not Between
CP, NP Contains Pattern,
does Not contain Pattern
LT, LE Less Than, Less Equal
GT, GE Greater Than, Greater Equal

LOW

When you use EQ, NE, LT, LE, GT, GE, this field contains the comparison value. When you use BT and NB, the field contains the lower limit. When you use CP and NP, the comparison value can be contained in both LOW and HIGH.

HIGH

When you use BT and NB, this field contains the upper limit. When you use CP and NP, it can contain the end of the template begun in LOW.

The elementary conditions contained in itab can be combined to form a complex condition:

• If itab is empty, the condition f IN itab is always true.
  
  
  
• If itab only contains the inclusive elementary conditions i1..., in, the combined condition is
  ( i1 OR ... OR in )
  
  
  
• If itab only contains the exclusive conditions e1, ..., em, the combined 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 combined condition is
  ( i1 OR ... OR in ) AND
  ( NOT e1 ) AND ... AND ( NOT em )
  
  
  

Example

Example to select customer numbers:

• '10000000' to '19999999',
  
• '01104711' and
  
• all customer numbers equal to '90000000',
  
  
  

but not customer numbers

• '10000810' to '10000815',
  
• '10000911' or
  
• any customer numbers whose fifth digit is '5':
  
  
  

DATA: WA_SCUSTOM TYPE SCUSTOM.

SELECT-OPTIONS: R FOR WA_SCUSTOM-ID.
* RANGES:       R FOR WA_SCUSTOM-ID.

* R is filled as follows (sequence of lines
* is irrelevant:
*
* SIGN  OPTION  LOW       HIGH
* --------------------------------
* I     EQ      01104711
* I     BT      10000000  19999999
* I     GE      90000000
* E     EQ      10000911
* E     BT      10000810  10000815
* E     CP      ++++5*
*
* So the condition is
*
* ( 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 INTO WA_SCUSTOM WHERE ID IN R.
  ...
ENDSELECT.

Notes

1. Since a condition in the form f IN itab becomes a complex condition at runtime, but the maximum length of an SQL statement is restricted by the database system (for example, to 8 KB), the internal table itab should not contain too many lines.
   
   
   
2. If the database field f contains the value NULL, the evaluation of the condition is neither "true" nor "false", but "unknown".
   
   
   
3. You cannot use this variant in the ON addition of the FROM clause.
   
   
   

Variant 7

f IS [NOT] NULL

Effect

The condition is true if the contents of table field f (does not) contain(s) the NULL value.

Example

Example to select all customers for whom no telephone number is specified:

DATA WA_SCUSTOM TYPE SCUSTOM.

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE IS NULL.
ENDSELECT.

Note

Performance:

The SAP buffer does not support this variant. Therefore, every SELECT command on a buffered table or a view with fields from buffered table that contains ... WHERE f IS [NOT] NULL behaves as though the FROM clause contained the BYPASSING BUFFER addition.

Variant 8

[NOT] EXISTS subquery

Effect

The condition is met for a table entry if the result set of the subquery subquery contains [does not contain] any lines.

Example

Selects the flights for which at least one booking exists:

DATA WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT
   WHERE EXISTS ( SELECT * FROM SBOOK
                     WHERE CARRID = F~CARRID
                       AND CONNID = F~CONNID
                       AND FLDATE = F~FLDATE ) .
   WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, WA_SFLIGHT-FLDATE.
ENDSELECT.

Note,,You cannot use this variant in the ON addition of the FROM clause.

Variant 9

NOT cond

Effect

NOT cond is true when cond is false. The condition is false when cond is true. This gives the following truth table:

-------------------------
| NOT       |           |
-------------------------
| true      | false     |
| false     | true      |
| unknown   | unknown   |
-------------------------

cond can be any condition according to WHERE variants 1 - 12. NOT is stronger than AND and OR. You can use parentheses to determine the sequence for analysis explicitly.

Note

Parentheses establishing the analysis sequence must be separated by spaces.

Example

Example to select customers with customer numbers who do not live in ZIP code area 68.

... WHERE NOT POSTCODE LIKE '68%'

Variant 10

cond1 AND cond2

Effect

cond1 AND cond2 is true if cond1 and cond2 are both true. The condition is false when either cond1 or cond2 is false. This gives the following truth table:

-------------------------------------------------
| AND       | true      | false     | unknown   |
-------------------------------------------------
| true      | true      | false     | unkown    |
| false     | false     | false     | false     |
| unknown   | unknown   | false     | unknown   |
-------------------------------------------------

cond1 and cond2 can be any conditions according to WHERE variants 1 - 12. AND is stronger than OR but weaker than NOT. You can determine the analysis sequence explicitly using parentheses.

Note

Parentheses determining the analysis sequence must be separated by spaces.

Example

Example to select customers with customer numbers lower than '01000000' and who do not live in ZIP code area 68:

... WHERE ID < '01000000'
          AND NOT
          POSTCODE LIKE '68%'

Variant 11

cond1 OR cond2

Effect

cond1 OR cond2 is true when cond1 or cond2 is true. The condition is false if both cond1 and cond2 are false. This gives 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 WHERE variants 1 - 12. OR is weaker than AND and NOT. You can determine the analysis sequence dynamically using parentheses.

Note

Parentheses determining the analysis sequence must be separated by spaces.

Examples

Example to select all customers with customer numbers less than '01000000' or greater than '02000000':

... WHERE ID < '01000000' OR
          ID > '02000000'.

Example to select customers with customer numbers less than '01000000' or greater than '02000000' who do not live in ZIP code areas 68 or 69:

... WHERE ( ID < '01000000' OR ID > '02000000' )
          AND NOT
          ( POSTCODE LIKE '68%' OR POSTCODE LIKE '69%' )

Variant 12

(source_text)

Effect

The condition is true if the contents of the table fields satisfy the dynamic logical condition stored in the variable source_text as ABAP source text. source_text is filled at runtime.

Additional help

Selecting Lines