Start of Content Area

Selecting Lines  Locate the document in its SAP Library structure

The WHERE clause restricts the number of lines selected by specifying conditions that must be met.

This graphic is explained in the accompanying text

As well as in the SELECT statement, the WHERE clause is also used in the OPEN CURSOR, UPDATE, and DELETE statements. The general form of the WHERE clause is:

SELECT ... WHERE <cond> ...

The <cond> conditions in the WHERE clause can be comparisons or a series of other special expressions. You can combine a series of conditions into a single condition. Conditions may also be programmed dynamically.

The conditions <cond> in the WHERE clause are often like logical expressions, but not identical, since the syntax and semantics follow that of Standard SQL. In the conditions in the WHERE clause, you name columns using a field name as in the SELECT clause. In the following descriptions, <s> always represents a column of one of the database tables named in the FROM clause. The result of a condition may be true, false, or unknown. A line is only selected if the condition is true for it. A condition is unknown if one of the columns involved contains a null value.

Comparisons for All Types

To compare the value of a column of any data type with another value, use the following:

SELECT ... WHERE <s> <operator> <f> ...

<f> can be another column in a database table from the FROM clause, a data object, or a scalar subquery.

You can use the following expressions for the relational operator:

<operator>

Meaning

EQ

equal to

=

equal to

NE

not equal to

<>

not equal to

><

not equal to

LT

less than

<

less than

LE

less than or equal to

<=

less than or equal to

GT

greater than

>

greater than

GE

greater than or equal to

>=

greater than or equal to

The values of the operands are converted if necessary. The conversion may be dependent on the platform and codepage.

Values in Intervals

To find out whether the value of a column lies within a particular interval, use:

SELECT ... WHERE <s> [NOT ] BETWEEN <f 1> AND <f 2> ...

The condition is true if the value of column <s> is [not] between the values of the data objects <f1> and <f 2>. You cannot use BETWEEN in the ON condition of the FROM clause.

Comparing Strings

To find out whether the value of a column matches a pattern, use:

SELECT ... WHERE <s> [NOT ] LIKE <f> [ESCAPE <h>] ...

The condition is true if the value of the column <s> matches [does not match] the pattern in the data object <f>. You can only use this test for text fields. The data type of the column must be alphanumeric. <f> must have data type C.

You can use the following wildcard characters in <f>:

For example, ABC_EFG% matches the strings ABCxEFGxyz and ABCxEFG, but not ABCEFGxyz. If you want to use the two wildcard characters explicitly in the comparison, use the ESCAPE option. ESCAPE <h> specifies an escape symbol <h>. If preceded by <h>, the wildcards and the escape symbol itself lose their usual function within the pattern <f>. The use of _ and % corresponds to Standard SQL usage. Logical expressions elsewhere in ABAP use other wildcard characters (+ and *).

You cannot use LIKE in the ON condition of the FROM clause.

Checking Lists of Values

To find out whether the value of a column is contained in a list of values, use:

SELECT ... WHERE <s> [NOT ] IN (<f 1>, ......, <f n>) ...

The condition is true if the value of column <s> is [not] in the list <f1> ... <f n>.

Checking Subqueries

To find out whether the value of a column is contained in a scalar subquery, use:

SELECT ... WHERE <s> [NOT ] IN <subquery> ...

The condition is true if the value of <s> is [not] contained in the results set of the scalar subquery <subquery>.

To find out whether the selection of a subquery contains lines at all, use:

SELECT ... WHERE [ NOT ] EXISTS <subquery> ...

This condition is true if the result set of the subquery <subquery> contains at least one [no] line. The subquery does not have to be scalar.

You cannot check a subquery in the ON condition of the FROM clause.

Checking Selection Tables

To find out whether the value of a column satisfies the conditions in a selection table, use:

SELECT ... WHERE <s> [NOT ] IN <seltab> ...

The condition is true if the value of <s> [does not] satisfy the conditions stored in <seltab>. <seltab> can be either a real selection table or a RANGES table. You cannot check a selection table in the ON condition of the FROM clause.

Checking for Null Values

To find out whether the value of a column is null, use:

SELECT ... WHERE <s> IS [NOT ] NULL ...

The condition is true if the value of <s> is [not] null.

Negating Conditions

To negate the result of a condition, use:

SELECT ... WHERE NOT <cond> ...

The condition is true if <cond> is false, and false if <cond> is true. The result of an unknown condition remains unknown when negated.

Linking Conditions

You can combine two conditions into one using the AND and OR operators:

SELECT ... WHERE <cond 1> AND <cond 2> ...

This condition is true if < cond1 > and < cond2 > are true.

SELECT ... WHERE <cond 1> OR <cond 2> ...

This condition is true if one or both of < cond1 > and < cond2 > are true.

NOT takes priority over AND, and AND takes priority over OR. However, you can also control the processing sequence using parentheses.

Dynamic Conditions

To specify a condition dynamically, use:

SELECT ... WHERE (<itab>) ...

where <itab> is an internal table with line type C and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written into the lines of <itab>. However, you may only use literals, and not the names of data objects. The internal table can also be left empty.

If you only want to specify a part of the condition dynamically, use:

SELECT ... WHERE <cond> AND (<itab>) ...

You cannot link a static and a dynamic condition using OR.

You may only use dynamic conditions in the WHERE clause of the SELECT statement.

Tabular Conditions

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Examples

Example

Conditions in the WHERE clause:

... WHERE CARRID = 'UA'.

This condition is true if the column CARRID has the contents UA.

... WHERE NUM GE 15.

This condition is true if the column NUM contains numbers greater than or equal to 15.

... WHERE CITYFROM NE 'FRANKFURT'.

This condition is true if the column CITYFROM does not contain the string FRANKFURT.

... WHERE NUM BETWEEN 15 AND 45.

This condition is true if the column NUM contains numbers between 15 and 45.

... WHERE NUM NOT BETWEEN 1 AND 99.

This condition is true if the column NUM contains numbers not between 1 and 99.

... WHERE NAME NOT BETWEEN 'A' AND 'H'.

This condition is true if the column NAME is one character long and its contents are not between A and H.

... WHERE CITY LIKE '%town%'.

This condition is true if the column CITY contains a string containing the pattern ‘town’.

... WHERE NAME NOT LIKE '_n%'.

This condition is true if the column NAME contains a value whose second character is not ‘n’.

... WHERE FUNCNAME LIKE 'EDIT#_%' ESCAPE '#'.

This condition is true if the contents of the column FUNCNAME begin with EDIT_.

... WHERE CITY IN ('BERLIN', 'NEW YORK', 'LONDON').

This condition is true if the column CITY contains one of the values BERLIN, NEW YORK, or LONDON.

... WHERE CITY NOT IN ('FRANKFURT', 'ROME').

This condition is true if the column CITY does not contain the values FRANKFURT or ROME.

... WHERE ( NUMBER = '0001' OR NUMBER = '0002' ) AND
      NOT ( COUNTRY = 'F' OR COUNTRY = 'USA' ).

This condition is true if the column NUMBER contains the value 0001 or 0002 and the column COUNTRY contains neither F nor USA.

Example

Dynamic conditions

DATA: COND(72) TYPE C,
      ITAB LIKE TABLE OF COND.

PARAMETERS: CITY1(10) TYPE C, CITY2(10) TYPE C.

DATA WA TYPE SPFLI-CITYFROM.

CONCATENATE 'CITYFROM = ''' CITY1 '''' INTO COND.
APPEND COND TO ITAB.
CONCATENATE 'OR CITYFROM = ''' CITY2 '''' INTO COND.
APPEND COND TO ITAB.
CONCATENATE 'OR CITYFROM = ''' 'BERLIN' '''' INTO COND.
APPEND COND TO ITAB.

LOOP AT ITAB INTO COND.
  WRITE COND.
ENDLOOP.

SKIP.

SELECT CITYFROM
INTO   WA
FROM   SPFLI
WHERE  (ITAB).

  WRITE / WA.

ENDSELECT.

If the user enters FRANKFURT and BERLIN for the parameters CITY1 and CITY2 on the selection screen, the list display is as follows:

This graphic is explained in the accompanying text

The first three lines show the contents of the internal table ITAB. Exactly the corresponding table lines are selected.

Example

Tabular conditions

DATA: BEGIN OF LINE,
        CARRID   TYPE SPFLI-CARRID,
        CONNID   TYPE SPFLI-CONNID,
        CITYFROM TYPE SPFLI-CITYFROM,
        CITYTO   TYPE SPFLI-CITYTO,
      END OF LINE,
      ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.
LINE-CITYTO   = 'BERLIN'.
APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.
LINE-CITYTO   = 'SAN FRANCISCO'.
APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO
INTO   CORRESPONDING FIELDS OF LINE
FROM   SPFLI
FOR ALL ENTRIES IN ITAB
WHERE  CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

  WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

The output is as follows:

This graphic is explained in the accompanying text

This example selects all lines in which the following conditions are fulfilled:

· The CITYFROM column contains FRANKFURT and the CITYTO column contains BERLIN.

· The CITYFROM column contains NEW YORK and the CITYTO column contains SAN FRANCISCO.

Example

Tabular conditions

DATA: TAB_SPFLI   TYPE TABLE OF SPFLI,
      TAB_SFLIGHT TYPE SORTED TABLE OF SFLIGHT
                       WITH UNIQUE KEY TABLE LINE,
      WA LIKE LINE OF TAB_SFLIGHT.

SELECT CARRID CONNID
INTO   CORRESPONDING FIELDS OF TABLE TAB_SPFLI
FROM   SPFLI
WHERE  CITYFROM  = 'NEW YORK'.

SELECT CARRID CONNID FLDATE
INTO   CORRESPONDING FIELDS OF TABLE TAB_SFLIGHT
FROM   SFLIGHT
FOR ALL ENTRIES IN TAB_SPFLI
WHERE  CARRID = TAB_SPFLI-CARRID AND
       CONNID = TAB_SPFLI-CONNID.

LOOP AT TAB_SFLIGHT INTO WA.

  AT NEW CONNID.
    WRITE: / WA-CARRID, WA-CONNID.
  ENDAT.

  WRITE: / WA-FLDATE.

ENDLOOP.

The output is as follows:

This graphic is explained in the accompanying text

This example selects flight data from SFLIGHT for all connections for which the column CITYFROM in table SPFLI has the value NEW YORK. You could also use a join in the FROM clause to select the same data in a single SELECT statement.