Tips for Safe Usage of FOR ALL ENTRIES
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.
Hi Umit,
Would you please consider moving this blog from your personal space into an SCN space, ABAP Development for example. Why? Take a look at this blog 🙂
Why move your blogs into SCN spaces?
Thanks and regards,
Mariana
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. 🙂
Have you executed the code you are using FoE and there is no relation.....
Without using any field of source table in where clause how we get usage of For all entries ?