WHERE Clause

Variants:

1. ... WHERE cond

2. ... FOR ALL ENTRIES IN itab WHERE cond

3. ... WHERE (source_text)

Effect

If you specify a WHERE clause in the SELECT, OPEN CURSOR, UPDATE or DELETE statement, the system only selects records from the table or tables in the FROM clause that meet the logical condition cond in it.

When you use OPEN SQL statements, the system uses automatic client handling by default. This means that, for a client-specific table, only data from the current client is processed. Specifying a condition for the client field in the WHERE clause does not make sense, and causes an error in the syntax check.

If you switch off the automatic client handlign using the ... CLIENT SPECIFIED addition in the FROM clause, the client field is treated like a normal field, and you can then specify conditions for it normally in the WHERE clause.

Notes

If you have transparent tables that you often access without using the full primary key, or where you often sort the data in a sequence that is different to that of the primary key, you should consider creating a corresponding index.
If you do not specify a WHERE condition in a selection, the system processes all table records in the current client.

Variant 1

... WHERE cond

Effect

Only selects the records that meet the WHERE condition cond.

Example

Displaying all Lufthansa flights:

DATA: WA_SPFLI TYPE SPFLI.

SELECT * FROM spfli INTO wa_spfli
WHERE carrid = 'LH'.
WRITE: / wa_spfli-cityfrom, wa_spfli-cityto.
ENDSELECT.

Variant 2

... FOR ALL ENTRIES IN itab WHERE cond

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type,
         carrid TYPE sflight-carrid,
         connid TYPE sflight-connid,
       END OF ftab_type.

DATA:  ftab TYPE STANDARD TABLE OF ftab_type WITH
                 NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,
       free TYPE I,
       wa_sflight TYPE sflight.

* Suppose FTAB is filled as follows:
*
* CARRID  CONNID
* --------------
* LH      2415
* SQ      0026
* LH      0400

SELECT * FROM sflight INTO wa_sflight
    FOR ALL ENTRIES IN ftab
    WHERE CARRID = ftab-carrid AND
          CONNID = ftab-connid AND
          fldate = '20010228'.
  free = wa_sflight-seatsocc - wa_sflight-seatsmax.
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.

* The statement has the same effect as:

SELECT DISTINCT * FROM sflight INTO wa_sflight
    WHERE ( carrid = 'LH'   AND
            connid = '2415' AND
            fldate = '20010228' ) OR
          ( carrid = 'SQ'   AND
            connid = '0026' AND
            fldate = '20010228' ) OR
          ( carrid = 'LH'   AND
            connid = '0400' AND
            fldate = '20010228' ).
  free = wa_sflight-seatsocc - wa_sflight-seatsmax.
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.

Notes

You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.
In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.
Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.
You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.
If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.

If you use FOR ALL ENTRIES IN itab, you cannot use a HAVING clause as well.

Variant 3

... WHERE (source_text)

Effect

Only selects records that fulfil dynamic logical condition containing the source_text variable as ABAP source code.

Example

Displays all flights on a specified date, after the user inputs the data and airline:

PARAMETERS: airline(2) TYPE C,
            date       TYPE D.
DATA: where_clause TYPE STRING,
      connid       TYPE sflight-connid.

CONCATENATE     'carrid = ''' airline ''''
            ' AND fldate = ''' date '''' INTO where_clause.

SELECT connid FROM sflight INTO connid
  WHERE (where_clause).
  WRITE: / date, airline, connid.
ENDSELECT.

Note

You can mix static (variants 1 and 2) and dynamic conditions (variant 3) any way you like in WHERE clauses.

Notes

Performance:

Specify all conditions in the WHERE clause. This means that you do not transport redundant data over the network only to filter it out in your program (using CHECK, for example).
If you regularly use a SELECT statement, you should create an index. In the WHERE clause, you should use the fields of the index, linked using AND, and checking for equality. Fields of an index that occur after a field for which you do not use an equality comparison in the WHERE clause (EQ or =) cannot be used to restrict the search.
The logical expression NOT in a WHERE clause is not supported by indexes. For example, WHERE FLDATE >= '20010228' is better than WHERE NOT FLDATE < '20010228'.

Additional help

Selecting Lines