internal table

[Former Member]

: what is the use of FOR ALL ENTRIES in an internal table?could u plzz tell?

[suresh_datti]

FOR ALL ENTRIES is an option in a SELECT statement. It helps to avoid opening the itab loop when selecting relevant entries from the db table. Pl search the forum & blogs with key word "FOR ALL ENTRIES", you will find a lot of hits & enough help.

~Suresh

[Former Member]

Hi,

When you write a SELECT statement to get the data from database, you can use FOR ALL ENTRIES addition. You use this addition in the case where you are getting multiple records from database and the selection ( WHERE condition ) is based on entries in some internal table. This way, instead of looping the internal table an getting single record at a time, you get all the records at once. This is one of the performance enhancement technique in SAP for SQL select.

Check this code for more undertstanding. Here,

- First the data has been read from table EKKO, EKPO and EKBE using join statement to get PO data.

- Then for all the entries in PO data table the invoice data has been read from table RBFP. So instead of looping at PO data and reading single record at a time, all the records are retrieved at once.

TABLES: ekko.

SELECT-OPTIONS: s_ebeln FOR ekko-ebeln.

DATA: BEGIN OF lit_podata OCCURS 0,
        ebeln LIKE ekpo-ebeln,
        ebelp LIKE ekpo-ebelp,
        matnr LIKE ekpo-matnr,
        netwr LIKE ekpo-netwr,
        navnw LIKE ekpo-navnw,
        bukrs LIKE ekko-bukrs,
        bsart LIKE ekko-bsart,
        lifnr LIKE ekko-lifnr,
        ekorg LIKE ekko-ekorg,
        gjahr LIKE ekbe-gjahr,
        belnr LIKE ekbe-belnr,
        buzei LIKE ekbe-buzei,
      END OF lit_podata.


DATA: BEGIN OF lit_invdata OCCURS 0,
        belnr    LIKE rbkp-belnr,
        gjahr    LIKE rbkp-gjahr,
        blart    LIKE rbkp-blart,
        bldat    LIKE rbkp-bldat,
        budat    LIKE rbkp-budat,
        xblnr    LIKE rbkp-xblnr,
      END OF lit_invdata.

DATA: BEGIN OF lit_output OCCURS 0,
        ebeln LIKE ekpo-ebeln,
        ebelp LIKE ekpo-ebelp,
        matnr LIKE ekpo-matnr,
        netwr LIKE ekpo-netwr,
        navnw LIKE ekpo-navnw,
        bukrs LIKE ekko-bukrs,
        bsart LIKE ekko-bsart,
        lifnr LIKE ekko-lifnr,
        ekorg LIKE ekko-ekorg,
        gjahr LIKE ekbe-gjahr,
        belnr LIKE ekbe-belnr,
        buzei LIKE ekbe-buzei,
        blart LIKE rbkp-blart,
        bldat LIKE rbkp-bldat,
        budat LIKE rbkp-budat,
        xblnr LIKE rbkp-xblnr,
      END OF lit_output.

*" Get PO data and PO history data
SELECT
      a~ebeln
      a~ebelp
      a~matnr
      a~netwr
      a~navnw
      b~bukrs
      b~bsart
      b~lifnr
      b~ekorg
      c~gjahr
      c~belnr
      c~buzei FROM ekko AS b
              INNER JOIN ekpo AS a
              ON a~ebeln = b~ebeln
              INNER JOIN ekbe AS c
              ON a~ebeln = c~ebeln AND
              a~ebelp = c~ebelp
              INTO TABLE lit_podata
        WHERE a~ebeln IN s_ebeln.


IF NOT lit_podata[] IS INITIAL.
*" get invoice data for retrieved PO data
*" data is retrieved at once using FOR ALL ENTRIES IN lit_podata.
  SELECT
        belnr
        gjahr
        blart
        bldat
        budat
        xblnr FROM rbkp
              INTO TABLE lit_invdata
              FOR ALL ENTRIES IN lit_podata
              WHERE belnr = lit_podata-belnr AND
                    gjahr = lit_podata-gjahr.

ENDIF.

SORT: lit_podata BY  ebeln
                     ebelp,
      lit_invdata BY belnr
                     gjahr.

*" move data to output table for display.
LOOP AT lit_podata.
  READ TABLE lit_invdata WITH KEY belnr = lit_podata-belnr
                                  gjahr = lit_podata-gjahr.

  IF sy-subrc = 0.
    MOVE-CORRESPONDING lit_podata TO lit_output.
    lit_output-blart = lit_invdata-blart.
    lit_output-bldat = lit_invdata-bldat.
    lit_output-budat = lit_invdata-budat.
    lit_output-xblnr = lit_invdata-xblnr.

    APPEND lit_output.
    CLEAR: lit_invdata, lit_output.
  ENDIF.
ENDLOOP.

IF lit_output[] IS INITIAL.
  WRITE:/ 'No data found' COLOR COL_NEGATIVE.
ELSE.
  LOOP AT lit_output.

    WRITE:/ lit_output-ebeln,
            lit_output-ebelp,
            lit_output-matnr,
            lit_output-netwr,
            lit_output-navnw,
            lit_output-bukrs,
            lit_output-bsart,
            lit_output-lifnr,
            lit_output-ekorg,
            lit_output-belnr,
            lit_output-buzei,
            lit_output-gjahr,
            lit_output-blart,
            lit_output-bldat,
            lit_output-budat,
            lit_output-xblnr.
  ENDLOOP.
ENDIF.

Let me know if you need any other information.

Regards,

RS

[RaymondGiuseppi]

It helps optimize the access to database in SELECT instruction.

<i>Minimize the Number of Data Transfers

In every Open SQL statement, data is transferred between the application server and the database system. Furthermore, the database system has to construct or reopen the appropriate administration data for each database access. You can therefore minimize the load on the network and the database system by minimizing the number of times you access the database.

Multiple Operations Instead of Single Operations

When you change data using INSERT, UPDATE and DELETE, use internal tables instead of single entries. If you read data using SELECT, it is worth using multiple operations if you want to process the data more than once, other wise, a simple select loop is more efficient.

Avoid Repeated Access

As a rule you should read a given set of data once only in your program, and using a single access. Avoid accessing the same data more than once (for example, SELECT before an UPDATE or DELETE).

Avoid Nested SELECT Loops

A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. You should therefore only use nested SELECT loops if the selection in the outer loop contains very few lines.

However, using combinations of data from different database tables is more the rule than the exception in the relational data model. You can use the following techniques to avoid nested SELECT statements:

ABAP Dictionary Views

You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. Such views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server.

Views in the ABAP Dictionary are implemented as inner joins. If the inner table contains no lines that correspond to lines in the outer table, no data is transferred. This is not always the desired result. For example, when you read data from a text table, you want to include lines in the selection even if the corresponding text does not exist in the required language. If you want to include all of the data from the outer table, you can program a left outer join in ABAP.

The links between the tables in the view are created and optimized by the database system. Like database tables, you can buffer views on the application server. The same buffering rules apply to views as to tables. In other words, it is most appropriate for views that you use mostly to read data. This reduces the network load and the amount of physical I/O in the database.

Joins in the FROM Clause

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

Subqueries in the WHERE and HAVING Clauses

Another way of accessing more than one database table in the same Open SQL statement is to use subqueries in the WHERE or HAVING clause. The data from a subquery is not transferred to the application server. Instead, it is used to evaluate conditions in the database system. This is a simple and effective way of programming complex database operations.

<b>Using Internal Tables

It is also possible to avoid nested SELECT loops by placing the selection from the outer loop in an internal table and then running the inner selection once only using the FOR ALL ENTRIES addition. This technique stems from the time before joins were allowed in the FROM clause. On the other hand, it does prevent redundant data from being transferred from the database.</b>

Using a Cursor to Read Data

A further method is to decouple the INTO clause from the SELECTstatement by opening a cursor using OPEN CURSOR and reading data line by line using FETCH NEXT CURSOR. You must open a new cursor for each nested loop. In this case, you must ensure yourself that the correct lines are read from the database tables in the correct order. This usually requires a foreign key relationship between the database tables, and that they are sorted by the foreign key. </i>

Regards

[Former Member]

For all entries clause is used with SELECT statement to select data from database comparing internal table. It is used as OUTER JOIN condition in SQL.

eg.

select matnr from mara

FOR ALL ENTRIES IN g_ITB "(Where itab may contain some relevetn info for comparison)

where marnt = g_itab-matnr.

here records only marching with matnr if itab will be selected.

Three important consideration about FOR ALL ENTRIES:

1. Ensure that g_itab is not initial. " Other wise it will select all enteries form dbtab.

2. It select only uniqe fields only. So sometime data returned may be worng . so u need to give non unique key combination like vbeln, posnr...

3. If itab it to big then it may affect performance. for smaller itab it is quite fast.

Jogdand M B

PS: Award points if helpful...

[Former Member]

pls go through this

<a href="http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm">http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm</a>

in tabular condition check that

regards

shiba dutta