about for all entries

[Former Member]

can u give the information abt the FOR ALL ENTRIES and the conditions.

[Former Member]

Hello,

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type, 
         carrid TYPE sflight-carrid, 
         connid TYPE sflight-connid, 
       END OF ftab_type. 

DATA:  ftab TYPE STANDARD TABLE OF ftab_type WITH 
                 NON-UNIQUE DEFAULT KEY INITIAL SIZE 10, 
       free TYPE I, 
       wa_sflight TYPE sflight. 

* Suppose FTAB is filled as follows: 
* 
* CARRID  CONNID 
* -------------- 
* LH      2415 
* SQ      0026 
* LH      0400 

SELECT * FROM sflight INTO wa_sflight 
    FOR ALL ENTRIES IN ftab 
    WHERE CARRID = ftab-carrid AND 
          CONNID = ftab-connid AND 
          fldate = '20010228'. 
  free = wa_sflight-seatsocc - wa_sflight-seatsmax. 
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free. 
ENDSELECT. 

* The statement has the same effect as: 

SELECT DISTINCT * FROM sflight INTO wa_sflight 
    WHERE ( carrid = 'LH'   AND 
            connid = '2415' AND 
            fldate = '20010228' ) OR 
          ( carrid = 'SQ'   AND 
            connid = '0026' AND 
            fldate = '20010228' ) OR 
          ( carrid = 'LH'   AND 
            connid = '0400' AND 
            fldate = '20010228' ). 
  free = wa_sflight-seatsocc - wa_sflight-seatsmax. 
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free. 
ENDSELECT.

NOTE: Dont forget to check whether the internal table is initial or not while using FOR ALL ENTRIES. If the itab is initial, it will pull all records from the dbtable.

Thanks,

Message was edited by: Naren Somen

[Former Member]

Hi,

FOR ALL ENTRIES is used when you want to retrieve data from a table for each and every row of data that exists in a internal table. So instead of looping at the table and calling the SELECT inside a loop, which will be a performance overhead, use the FOR ALL ENTRIES clause.

You need to careful that the internal table that you are using in the WHERE clause is not empty. If it is, then the system will fetch all the rows of the table from which the data is being selected from.

For further reading and syntax details

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/frameset.htm

Regards,

Ravi

Note : Please mark the helpful answers

[Former Member]

FOR ALL ENTRIES IN itab WHERE cond

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

END OF ftab_type.

DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH

NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

free TYPE I,

wa_sflight TYPE sflight.

• Suppose FTAB is filled as follows:

• CARRID CONNID

• --------------

• LH 2415

• SQ 0026

• LH 0400

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

• The statement has the same effect as:

SELECT DISTINCT * FROM sflight INTO wa_sflight

WHERE ( carrid = 'LH' AND

connid = '2415' AND

fldate = '20010228' ) OR

( carrid = 'SQ' AND

connid = '0026' AND

fldate = '20010228' ) OR

( carrid = 'LH' AND

connid = '0400' AND

fldate = '20010228' ).

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

[Former Member]

Hi

Refer the following links

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

http://www.sapdevelopment.co.uk/tips/tips_select.htm

Regards

Beena

[Former Member]

HI

GOOD

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

GO THROUGH THESE LINKS

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

CODE

-

---

Code to demonstrate Select FOR ALL ENTRIES command

• The FOR ALL ENTRIES comand only retrieves data which matches

• entries within a particular internal table.

TYPES: begin of t_bkpf,

• include structure bkpf.

bukrs like bkpf-bukrs,

belnr like bkpf-belnr,

gjahr like bkpf-gjahr,

BLDAT like bkpf-BLDAT,

monat like bkpf-monat,

budat like bkpf-budat,

xblnr like bkpf-xblnr,

awtyp like bkpf-awtyp,

awkey like bkpf-awkey,

end of t_bkpf.

data: it_bkpf type standard table of t_bkpf initial size 0,

wa_bkpf type t_bkpf.

TYPES: begin of t_bseg,

*include structure bseg.

bukrs like bseg-bukrs,

belnr like bseg-belnr,

gjahr like bseg-gjahr,

buzei like bseg-buzei,

mwskz LIKE bseg-mwskz, "Tax code

umsks LIKE bseg-umsks, "Special G/L transaction type

prctr LIKE bseg-prctr, "Profit Centre

hkont LIKE bseg-hkont, "G/L account

xauto like bseg-xauto,

koart like bseg-koart,

dmbtr like bseg-dmbtr,

mwart like bseg-mwart,

hwbas like bseg-hwbas,

aufnr like bseg-aufnr,

projk like bseg-projk,

shkzg like bseg-shkzg,

kokrs like bseg-kokrs,

end of t_bseg.

data: it_bseg type standard table of t_bseg initial size 0,

wa_bseg type t_bseg.

select bukrs belnr gjahr BLDAT monat budat xblnr awtyp awkey

up to 100 rows

from bkpf

into table it_bkpf.

if sy-subrc EQ 0.

select bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

from bseg

into table it_bseg

FOR ALL ENTRIES in it_bkpf

where bukrs eq it_bkpf-bukrs and

belnr eq it_bkpf-belnr and

gjahr eq it_bkpf-gjahr.

endif.

CODE

-

---

You can only use FOR ALL ENTRIES IN ...WHERE ...in a SELECT statement.

SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT

statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol

itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result

set. If the internal table itab does not contain any entries, the system treats the statement as though there were

no WHERE cond condition, and selects all records (in the current client).

for example:

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

this condition, return all entries of the sflight

THANKS

MRUTYUN

[abdul_hakim]

hi naidu,

FOR ALL ENTRIES can be used to replace the joins and it will yield better performance than joins.

Note: you should check whether the internal table is empty before using FOR ALL ENTRIES.If the internal table is empty then the select statement will fetch all the records from the database table regardless of the conditions.

for eg:

if not int_tab1[] is initial.

select * from tab2 into table int_tab2 for all entries in int_tab1 where kunnr = int_tab1-kunnr.

endif.

if you have not used the if condition and if the internal table int_tab1 is empty the select query will fetch all the records from tab2 regardless of the conditions..

Cheers,

Abdul Hakim

Mark all useful answers..

[Former Member]

Hi Rosaiah,

The most important this about For all entries is that while writing a select statement you have to take all the key fields otherwise it will not give you right result.

Example :

SELECT obknr sernr obzae

INTO CORRESPONDING FIELDS OF TABLE i_objk

FROM objk

FOR ALL ENTRIES IN i_ser03

WHERE obknr = i_ser03-obknr.

Here in the table OBJK three key fields are there

'obknr sernr obzae'.So whether you need all the three fields or not,in the selct statement you should select all the three fields.

Regards,

Mukesh KUmar

[Former Member]

for all entries is like this

loop at vbak.

select * from vbap

where vbeln = vbak-vbeln

endloop.

[Former Member]

Hi Rosaiah,

Consider this code.

REPORT abc.

TABLES : mara, marc.
TYPES :  BEGIN OF tp_mara,
         matnr TYPE mara-matnr,
         mtart TYPE mara-mtart,
         END OF tp_mara.


TYPES : BEGIN OF tp_marc,
        matnr TYPE mara-matnr,
        werks TYPE marc-werks,
        pstat TYPE marc-pstat,
        END OF tp_marc.

TYPES :  BEGIN OF tp_final,
         matnr TYPE mara-matnr,
         mtart TYPE mara-mtart,
         werks TYPE marc-werks,
         pstat TYPE marc-pstat,
         END OF tp_final.

DATA: start_time   TYPE sy-uzeit,
      end_time     TYPE sy-uzeit,
      difference   TYPE sy-uzeit.

DATA : t_mara    TYPE STANDARD TABLE OF tp_mara,
       t_marc    TYPE STANDARD TABLE OF tp_marc,
       t_frall   TYPE STANDARD TABLE OF tp_final,
       t_injoin  TYPE STANDARD TABLE OF tp_final.

DATA : wa_mara  TYPE tp_mara,
       wa_marc  TYPE tp_marc,
       wa_frall TYPE tp_final.


DATA : w_flreads TYPE  sy-tabix,
       w_inreads TYPE  sy-tabix.

SELECT-OPTIONS: s_matnr FOR mara-matnr.



START-OF-SELECTION.

  REFRESH : t_mara, t_marc, t_injoin, t_frall.
  PERFORM join_select.
  PERFORM forall_select.


*&--------------------------------------------------------------------*
*&      Form  join_select
*&--------------------------------------------------------------------*
*       text
*---------------------------------------------------------------------*
FORM join_select.

  GET TIME FIELD start_time.

  SELECT a~matnr a~mtart b~werks b~pstat
  INTO TABLE t_injoin
  FROM mara AS a INNER JOIN marc AS b
         ON a~matnr = b~matnr
      WHERE a~matnr IN s_matnr.
  w_inreads = sy-dbcnt.

  GET TIME FIELD end_time.

  difference = end_time - start_time.

  WRITE:   /001 'Time for getting consolidating data using INNER JOIN:', difference,
           /005 'Number of entries:', w_inreads.



ENDFORM.                    "join_select


*&--------------------------------------------------------------------*
*&      Form  forall_select
*&--------------------------------------------------------------------*
*       text
*---------------------------------------------------------------------*
FORM forall_select.


  GET TIME FIELD start_time.


  SELECT matnr mtart  INTO TABLE t_mara FROM mara
  WHERE matnr IN s_matnr.

  IF NOT t_mara[] IS INITIAL.
    SELECT matnr werks pstat  INTO TABLE t_marc
    FROM marc
    FOR ALL ENTRIES IN t_mara
    WHERE matnr = t_mara-matnr.
  ENDIF.

  SORT t_mara BY matnr.
  SORT t_marc BY matnr.

  CLEAR : wa_marc, wa_mara, wa_frall, w_flreads.

  LOOP AT t_marc INTO wa_marc.

    READ TABLE t_mara INTO wa_mara WITH KEY matnr = wa_marc-matnr.

    wa_frall-matnr = wa_mara-matnr.
    wa_frall-mtart = wa_mara-mtart.
    wa_frall-werks = wa_marc-werks.
    wa_frall-pstat = wa_marc-pstat.

    APPEND wa_frall TO t_frall.

    w_flreads = w_flreads + 1.


  ENDLOOP.


  GET TIME FIELD end_time.

  difference = end_time - start_time.

  WRITE:   /001 'Time for getting consolidating data using FOR ALL ENTRIES:', difference,
           /005 'Number of entries:', w_flreads.


ENDFORM.                    "join_select

Regards,

Arun Sambargi.

[Former Member]

HI

FOR ALL ENTRIES is really useful when u want to restrict selections bassed on the fields already present in another internal table.

Lets say u have selected some data from some header table and u want to restrict the line items select. u can use for all entries to filter out the line items form the header table.

Performance wise it might just be better than using nested joins .

PL reward points for helpful posts.