For All Entries

[Former Member]

HI All,

1. What is ment by for all entries in SAP ABAP.

2. How to write code for for all entries.

3. What is main pre-requisite for for all entries.

Bye,

Srinivas

[Former Member]

Hi Srinu,

For all entries is used to replace complex and time taking Joins.

2.

select vbeln from vbak

into table i_vbeln

where vblen in s_vbeln.

if sy-subrc = 0.

select vbeln posnr

from vbap

into table i_vbap

for all entries in i_vbak

where vbeln = i_vbap-vbeln.

endif.

3. pre requisites

• make sure the driver table(in the ex: i_vbak), is not initial

• sort the internal table before deleting duplicate entries.

• delete the duplicate entries before using for all entries.

in the ex. above, vbeln is unique and hence no duplicate entries.

regards,

madhu

[Former Member]

Hi

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:

1.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.

2.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.

3.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.

Wat exactly FOR ALL ENTRIES means:

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

thnx

Sravani

Plz reward points if helpful....

[Former Member]

Hi

here is a select query where i have used for all entries...just check...

PARAMETER : P_NO TYPE EKKO-EBELN OBLIGATORY.

SELECT EBELN LIFNR

FROM EKKO

INTO TABLE IT_EKKO

WHERE EBELN = P_NO.

SELECT LIFNR NAME1 LAND1 ORT01 REGIO

FROM LFA1

INTO TABLE IT_LFA1 FOR ALL ENTRIES IN IT_EKKO

WHERE LIFNR = IT_EKKO-LIFNR.

SELECT EBELP EMATN AEDAT NETWR MTART

FROM EKPO

INTO TABLE IT_EKPO FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN .

[Former Member]

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

For example -

DATA: TAB_SPFLI   TYPE TABLE OF SPFLI,
      TAB_SFLIGHT TYPE SORTED TABLE OF SFLIGHT
                       WITH UNIQUE KEY TABLE LINE,
      WA LIKE LINE OF TAB_SFLIGHT.

SELECT CARRID CONNID
INTO   CORRESPONDING FIELDS OF TABLE TAB_SPFLI
FROM   SPFLI
WHERE  CITYFROM  = 'NEW YORK'.

SELECT CARRID CONNID FLDATE
INTO   CORRESPONDING FIELDS OF TABLE TAB_SFLIGHT
FROM   SFLIGHT
FOR ALL ENTRIES IN TAB_SPFLI
WHERE  CARRID = TAB_SPFLI-CARRID AND
       CONNID = TAB_SPFLI-CONNID.

LOOP AT TAB_SFLIGHT INTO WA.

  AT NEW CONNID.
    WRITE: / WA-CARRID, WA-CONNID.
  ENDAT.

  WRITE: / WA-FLDATE.

ENDLOOP.

When using FOR ALL ENTRIES you need to make sure that table which you are using should not be empty else it will SELECT all the data from that table.

I hope it helps.

Thanks,

Vibha

Please mark all the useful answers

[former_member181995]

Its a replacement of innerjoin.

prerequesite:for internal table on which you are using for all entries that table is should not be empty.like here it_mkpf .

SELECT

budat " migo date

mblnr " migo number

xblnr " reference migo no

tcode2 " tcode

mjahr " year

FROM mkpf

INTO TABLE it_mkpf

WHERE mblnr LIKE '5%'

AND ( mjahr = '2008'

OR mjahr = '2007' )

AND budat IN so_budat

AND tcode2 = 'MIGO_GR' .

"DELETE it_mkpf[] WHERE budat not IN so_budat. " 25.03.2008

IF it_mkpf[] IS INITIAL.

MESSAGE i002(zamit).

LEAVE LIST-PROCESSING.

ENDIF.

SORT it_mkpf[] BY mblnr.

SELECT werks " plant

mblnr " migo no

matnr " material no

menge " migo qty

erfmg " migo FA

lifnr " Supplier

dmbtr " value

bpmng " PO qty

ebeln " po no

ebelp " po item no

elikz " close or not

mjahr " year

FROM mseg

INTO TABLE it_mseg

FOR ALL ENTRIES IN it_mkpf

WHERE mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

"AND mblnr LIKE '5%'

AND werks IN p_werks.

Hope this would help.

please reward points if clear.

Amit.

[former_member609120]

For all Entries can be used instead of JOIN between 2 tables

Prerequisite: The table used in for all entries in not empty and has some common fields with the table you want to fetch data into.

Example:

SELECT * FROM QALS INTO CORRESPONDING FIELDS OF TABLE I_DATA WHERE PRUEFLOS IN S_PRUEF AND

HERKUNFT EQ '89' AND ENSTEHDAT IN S_CDATE AND

MATNR IN S_MATNR .

IF NOT I_DATA IS INITIAL.

SELECT MATNR MAKTX FROM MAKT INTO TABLE I_MAKT FOR ALL ENTRIES IN I_DATA

WHERE MATNR = I_DATA-MATNR .

ENDIF