Skip to Content
Personal Insights
Author's profile photo Amol Samte

For All Entries Alternatives

Hello Folks,

Over a time period we are seeing new changes in ABAP and hope will come more and more to get work easy and interesting.

As a developer we always try to write code in optimized way, but whenever coming across a situation to enhance standard functionality like user exits, enhancements , adding extra functionality to copied Adobe Form etc.. some times we have to carry forward logic using existing data source or internal table with the help of  For All Entries. For All Entries is nothing but a join to an internal table.

For All Entries Sap Help :-

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenwhere_logexp_itab.htm

But it has some limitations and restriction to use SQL expressions and Order by clause with field names etc..

But we have some alternatives –

  1. Pushing Internal table to DB by using AMDP – After release 7.40
  2. Usage GTT (Global Temporary table) –  Before release 7.52
  3. Direct select from internal table – From release 7.52

Lets have an example –

1.Pushing Internal table to DB by using AMDP

Please go thought below link to understand how to push internal table to AMDP method and usage.

Note : Please read comments of below shared link blog for fair usage.

https://blogs.sap.com/2015/04/29/handle-internal-table-inside-amdp/

2. Using GTT

Create a GTT to hold Airline code and Flight Connection Number

After Creation of GTT table and insertion of records into GTT we can write direct query, Please see below example.

The GTT concept specifies that a GTT is always empty at the start of a database LUW and hence always has to be cleared at the end of each database LUW.

"Insert records into Global Temporary Table
  INSERT zgtt_flight FROM @(  VALUE #( carrid = 'LH' connid = 2402 ) ).

  SELECT a~carrid,
         a~connid,
         SUM( b~price ) AS price,
         b~currency

         FROM zgtt_flight AS a LEFT OUTER JOIN sflight AS b
         ON a~carrid = b~carrid
         AND a~connid = b~connid

         GROUP BY a~carrid, a~connid, b~currency

         ORDER BY a~carrid

         INTO TABLE @DATA(lt_itab).

  IF sy-subrc = 0.
    "The GTT concept specifies that a GTT is always empty at the start of a database LUW
    "hence always has to be cleared at the end of each database LUW otherwise will give dump
    "Runtime Errors COMMIT_GTT_ERROR
    DELETE FROM zgtt_flight.
  ENDIF.

 

Output :

For more about GTT

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abenddic_database_tables_gtt.htm

 

3. Using Internal table as Data Source From Release 7.52

The SELECT statement handles the internal table of the application server like a database table on the database and internal table must be prefixed with @ symbol as a data source of a query.

Below is the example :-

TYPES : BEGIN OF lty_flt,
            carrid TYPE s_carr_id,
            connid TYPE s_conn_id,
          END OF lty_flt.
  DATA : lt_flt TYPE STANDARD TABLE OF lty_flt.
  "Insert records into local internal table
  lt_flt =  VALUE #( ( carrid = 'LH' ) ( connid = 2402 ) ) .

  SELECT a~carrid,
         a~connid,
         SUM( b~price ) AS price,
         b~currency

         FROM @lt_flt AS a LEFT OUTER JOIN sflight AS b
         ON a~carrid = b~carrid
         AND a~connid = b~connid

         GROUP BY a~carrid, a~connid, b~currency

         ORDER BY a~carrid

         INTO TABLE @DATA(lt_itab)
  ##db_feature_mode[itabs_in_from_clause].

Output : –

For more about Internal Table as Data Source

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm

 

Well, hope this idea will be useful and always refer the sap help documentation for best practice.

 

Cheers,

Amol

Assigned tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srinivasa Mukka
      Srinivasa Mukka

      I just came to know that is also possible, nice blog man...

      Regards,

      Sri

      Author's profile photo Kevin Andre
      Kevin Andre

      Long Live ABAP....very informative content.
      Exactly I was looking for it

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Other blog posts about GTT:

      Author's profile photo Ketan Ag
      Ketan Ag

      Where can I get access of ABAP 7.52?

      Author's profile photo Amol Samte
      Amol Samte
      Blog Post Author

      Thanks Sandra..

      Author's profile photo Ketan Ag
      Ketan Ag

      Hi Amol,

      Very well blog... How I can get a access of ABAP 7.52?

      Regards,

      K

      Author's profile photo Amol Samte
      Amol Samte
      Blog Post Author

      here is link https://blogs.sap.com/2018/09/13/as-abap-752-sp01-developer-edition-to-download/

      Author's profile photo Paul Hardy
      Paul Hardy

      I would also advocate reading the SAP Press book about ABAP Development on HANA.

      My understanding (based on what i read there) is that when a FOR ALL ENTRIES is passed to a HANA database it automatically turns the selection table into a temporary database table, and then does an INNER JOIN on that temporary table and the other real tables being queried.

      From an SQL perspective (e.g. an ST05 trace) that means you get one big SQL statement, rather than the hundreds of lines you see when looking at ST05 for a FOR ALL ENTRIES in a non HANA database, each line having a description such as XYZ IN (A,B,C,D,E) OR... and so on.

      So in 7.52 with a non-HANA database you have to stuff around with doing this yourself, with a HANA database your existing FOR ALL ENTRIES will magically improve themselves.

      There are a bucket load of "under the hood" optimisations like this in a HANA database, which is why it is so much better than people think....

       

      Author's profile photo Urmi S
      Urmi S

      Point 3 Using Internal table as Data Source From Release 7.52 will be very good, I am eagerly waiting for 7.52 system..

      Nice blog....

      Regards,

      Uri S

      Author's profile photo Syambabu Allu
      Syambabu Allu

      Hi Amol,

      Good information.Thank you for sharing.

      Thanks,

      Syam

       

      Author's profile photo Pramod Teewaree
      Pramod Teewaree

      Hello,

      Thanks for the article.

      Could you please tell me why we get the warning "The SELECT command is executed on the database." when doing a SELECT on the internal table?

      I understand we can hide the warning using the pragma

      ##ITAB_DB_SELECT

      but I would still like to understand this message.

      Thanks.