Skip to Content

ABAP provides useful commands for selecting data from database tables. One of them is FOR ALL ENTRIES. This keyword is used before the WHERE statements. When FOR ALL ENTRIES is supplied for the SQL queries, WHERE condition will be applied for the internal table defined after for all entries.

Consider the following code snippet:

DATA:
ls_customer type KNA1,
 lt_customer type table of KNA1,
lt_results type table of KNA1.
ls_customer-KUNNR =  '5010010346'.
ls_customer-LAND1 = 'DE'
ls_customer-KUNNR =  '5010010347'.
ls_customer-LAND1 = 'TR'
APPEND ls_customer  TO lt_customer.
SORT lt_customer BY kunnr.
SELECT * INTO TABLE lt_results
FROM KNA1
FOR ALL ENTRIES IN lt_customer
WHERE LAND1 = 'DE'.

In the execution of query LAND1=’DE’ first applied to lt_customer. After that database select is sent with the table entries that is filtered. Since the where condition is applied to internal table it makes significant performance improvement.

Consider the case where the internal table is empty.

DATA:
ls_customer type KNA1,
 lt_customer type table of KNA1,
lt_results type table of KNA1.
APPEND ls_customer  TO lt_customer.
SELECT * INTO TABLE lt_results
FROM KNA1
FOR ALL ENTRIES IN lt_customer
WHERE LAND1 = 'DE'.

You assume that KNA1 is queried for LAND1 = ‘DE’. Wrong! If internal table is empty WHERE condition has no importance. WHERE condition is omitted. This causes all KNA1 is selected!

To use FOR ALL ENTRIES keyword safely

1 – Do not forget to check if the internal table that’s pass to FOR ALL ENTRIES is not empty

2 – SORT the internal table

Our code will look like this as result

DATA:
ls_customer type KNA1,
 lt_customer type table of KNA1,
lt_results type table of KNA1.
ls_customer-KUNNR =  '5010010346'.
ls_customer-LAND1 = 'DE'
ls_customer-KUNNR =  '5010010347'.
ls_customer-LAND1 = 'TR'
APPEND ls_customer  TO lt_customer.
     IF lt_customer IS NOT INITIAL.
     SORT lt_customer BY kunnr.
     SELECT * INTO TABLE lt_results
     FROM KNA1
     FOR ALL ENTRIES IN lt_customer
     WHERE LAND1 = 'DE'.
ENDIF.
To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Muthukumar Pasupathy

    Hi Umit,

    Nice blog. However, I do not see the rationale behind your insisting that the the “driver table” of a SELECT..FOR ALL ENTRIES statement must be sorted before the FOR ALL ENTRIES query is executed.

    As far as my knowledge goes, if the driver table has got several duplicate entries, then one would consider making a copy of that driver table and then removing the duplicates (in this process of removing duplicates, a SORT and a subsequent DELETE ADJACENT DUPLICATES FROM itab_copy_of_driver_table COMPARING..would be necessary). This is for improving the performance of a FOR ALL ENTRIES query. This is where sorting of the driver table would be necessary, as far as I know.

    So could you please explain the reason why the driver table has to be sorted before executing the FOR ALL ENTRIES query?

    Thanks for your time and effort.

    PS: In your FOR ALL ENTRIES query, the driver table: LT_CUSTOMER is not an operand in the WHERE clause expression. I am not sure if that is allowed syntactically. ๐Ÿ™‚

    (0) 

Leave a Reply