ABAP FOR ALL ENTRIES SELECT statement addition in SAP data retrieval

The ABAP select statement command has a number of options and additions which allows you to fine tune your database selection. One such addition is the 'FOR ALL ENTRIES' statement. This allows you to build an internal table and then restrict thew second database select to only get entries contained in this itab.

Below is a simple ABAP code example of how to select data using the 'FOR ALL ENTRIES' addition! It basically builds an internal table containing a list of purchase order numbers(EBELN) from table EKKO and then only retrieves entries from EKPO where the PO number exists in this internal table.

There is a strange feature that happens if you use an empty internal table as the for entries restriction along side a basic where clause.. see below for full details and example ABAP code.

REPORT  ZSELECTCOMMAND.
*Select FOR ALL ENTRIES command
data: it_ekko type STANDARD TABLE OF ekko,
      wa_ekko like line of it_ekko,
      it_ekpo type standard table of ekpo,
      wa_ekpo like line of it_ekpo.
SELECT *
  UP TO 10 ROWS  "only return first 10 hits
  FROM ekko
  INTO TABLE it_ekko.
loop at it_ekko into wa_ekko.
  write:/ wa_ekko-ebeln.
endloop.
IF sy-subrc EQ 0.
* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.
  SELECT *
    FROM ekpo
    INTO TABLE it_ekpo
    FOR ALL ENTRIES IN it_ekko
    WHERE ebeln EQ it_ekko-ebeln.
  write:/ 'FOR ALL ENTRIES comand '.
  loop at it_ekpo into wa_ekpo.
    write:/ wa_ekpo-ebeln, wa_ekpo-ebelp.
  endloop.
ENDIF.

Empty FOR ALL ENTRIES itab ignores standard where clause

This leads me onto the strange feature mentioned above that I would not really expect to happen, although I'm sure there is a logical reason for it. If you use the FOR ALL ENTRIES addition, but use an itab that is empty and you also use a standard where clause entry such as where "EBELN EQ WA_EKKO-EBELN" what would you expect to happen?

....Remember an empty for all entries table returns everything!!! but would you expect it to still restrict on the other where clause entry? See code below for demonstration of what happens (SPOILER ALERT: EBELN EQ WA_EKKO-EBELN is ignored and all entries are retrieved).

*Empty for all entries table
DATA: gd_lines TYPE i.
SELECT *
   FROM ekpo
   INTO TABLE it_ekpo
   WHERE ebeln EQ wa_ekko-ebeln.
DESCRIBE TABLE  it_ekpo LINES gd_lines.
WRITE:/ 'Number of lines without for all entries:', gd_lines.
*Restricts based on entries in it_ekko and ebeln EQ wa_ekko-ebeln
SELECT *
  FROM ekpo
  INTO TABLE it_ekpo
  FOR ALL ENTRIES IN it_ekko
  WHERE ebeln EQ it_ekko-ebeln
    AND ebeln EQ wa_ekko-ebeln.
DESCRIBE TABLE  it_ekpo LINES gd_lines.
WRITE:/ 'Number of lines with for all entries itab:', gd_lines.
*Only difference to the above code is that it_ekko is empty
REFRESH it_ekko.
SELECT *
 FROM ekpo
 INTO TABLE it_ekpo
 FOR ALL ENTRIES IN it_ekko
 WHERE ebeln EQ it_ekko-ebeln
   AND ebeln EQ wa_ekko-ebeln.
DESCRIBE TABLE  it_ekpo LINES gd_lines.
WRITE:/ 'Number of lines with empty for all entries itab:', gd_lines.

Result output


Related Articles

ABAP COLLECT statement syntax to add up all numeric internal table values within SAP
ABAP DELETE statement keyword to delete data from SAP internal and database tables
ABAP DESCRIBE statement keyword to get information about tables and fields
PERFORM TABLES command passing internal table as parameter
ABAP read command to read line of internal table in SAP
ABAP UPDATE command to modify database field values
AUTHORITY-CHECK abap Statement / command
ABAP delete command in SAP
ABAP MODIFY statement to update SAP data within database and internal tables
ABAP WRITE statement command with in SAP to display data report to users
SAP ABAP Statement syntax including basic implementation code
Concatenate ABAP statement syntax for concatenating data objects, sting values or rows of an SAP internal table
ABAP EXPORT data TO MEMORY ID and import it back again
IF, CHECK & WHEN ABAP commends example source code and information
Call Function module IN BACKGROUND TASK - Execute abap code in seperate background process
Function module IN UPDATE TASK statement - Execute abap code in seperate unit of work
ABAP STRLEN command to get the value length of a SAP field
SAP ABAP SELECT command and its different uses
SELECT..ENDSELECT command
ABAP SELECT inner join statement to select from two tables at the same time
SELECT directly into an internal table
SELECT directly into an internal table when field order is different
Function module STARTING NEW TASK statement - Execute abap code in seperate work process