“For All Entries” – Pitfalls, Limitations, Workarounds

“For All Entries” is a useful ABAP command used in the following basic format:

SELECT a b c FROM dbtab
FOR ALL ENTRIES IN git_itab
WHERE fld1 EQ git_itab-fld1
AND fld2 EQ git_itab-fld2.

However, it has two pitfalls I have witnessed to be ignored by ABAP programmers occasionally.

PITFALL 1: If git_itab is empty, the query will return all entries in dbtab – just like using an empty range in a query. Therefore, a “FOR ALL ENTRIES” query must have a trailing check to ensure that git_itab contains line items – unless you intentionally want to select all records in dbtab in the case where git_itab is empty (I never had such a need)

PITFALL 2: If dbtab has multiple entries having the same “a b c “ value, only the first one will be returned. Therefore, make sure that you select all of the key fields of the source table(s) – unless you intentionally want to select only the first occurrence of many “a b c” combinations (I never had such a need).

Although not a pitfall, there is also an annoying limitation: You can’t use aggregate functions in “For All Entries” queries. Here is my workaround:

• Create a Z-Table having the same structure with git_itab (let’s call it ZTEMP)
• Before the query, fill this table with the data in git_itab
• Run your aggregate query with an additional INNER JOIN to ZTEMP
• Delete the data in ZTEMP

If your program is executed by multiple users at the same time, their data in ZTEMP may be mixed. To prevent this risk, each execution must have a unique identifier. You can easily generate such an identifier through a number taken from SNRO or a GUID generated via the class CL_SYSTEM_UUID – let’s call it “GUID”. Add the “GUID” field to git_itab and ZTEMP as a key field; and imagine it like a secondary MANDT field. Every time you access ZTEMP, access it using the “GUID” value as well – this will isolate the data from each execution.

Here is the workaround in semi-pseudo code:

* Fill git_itab

“todo

* Create execution ID

lfd_guid = cl_system_uuid=>create_uuid_x16_static( ).

* Prepare temporary table

LOOP AT git_itab REFERENCE INTO lrd_itab.
  lrd_itab->guid = lfd_guid.
ENDLOOP.

INSERT ztemp FROM TABLE git_itab.

* Select aggregate data

SELECT
  dbtab~a dbtab~b dbtab~c SUM( dbtab~e ) AS e
  INTO CORRESPONDING FIELDS OF TABLE git_target
  FROM
    dbtab
    INNER JOIN ztemp 
       ON ztemp~a eq dbtab~a
      AND ztemp~b eq dbtab~b
      AND ztemp~c eq dbtab~c
      AND ztemp~guid eq lfd_guid
  WHERE ((additional conditions))
  GROUP BY dbtab~a dbtab~b dbtab~c.

* Purge temporary table

DELETE ztemp WHERE guid eq lfd_guid.

Advertisement