for all entries

[Former Member]

what is main purpose of for all entries?

Regards,

swathi.

[Former Member]

Hi,

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.

[Former Member]

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.

also see

Usage of 'for all entries' in Select Statement

FORM data_retrieval.

DATA: ld_color(1) TYPE c.

DATA: BEGIN OF T_VBAP OCCURS 0,

VBELN LIKE VBAP-VBELN,

MATNR LIKE VBAP-MATNR,

POSNR LIKE VBAP-POSNR,

END OF T_VBAP.

DATA: BEGIN OF T_VBFA OCCURS 0,

VBELV LIKE VBFA-VBELV,

VBELN LIKE VBFA-VBELN,

VBTYP_N LIKE VBFA-VBTYP_N,

END OF T_VBFA.

DATA: BEGIN OF T_VBAK OCCURS 0,

VBELN LIKE VBAK-VBELN,

IHREZ LIKE VBAK-IHREZ,

END OF T_VBAK.

DATA: BEGIN OF T_KNA1 OCCURS 0,

KUNNR LIKE KNA1-KUNNR,

NAME1 LIKE KNA1-NAME1,

END OF T_KNA1.

DATA: BEGIN OF T_MAKT OCCURS 0,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

END OF T_MAKT.

SELECT likp_{vbeln likp}lifex likp_{bldat likp}wadat likp_{wadat_ist likp}kodat likp~lfart

likp_{kunnr likp}vstel lips_{posnv lips}lfimg lips_{vrkme lips}lgmng lips~meins

lips_{werks lips}lgort lips_{charg lips}vbelv lips_{posnr lips}matnr

lips_{vbeln LIPS}VGBEL LIPS_{VGPOS vbup}kosta vbup_{wbsta vbup}posnr vbup~vbeln

• VBAK_{IHREZ VBAK}VBELN VBAP~VBELN

INTO CORRESPONDING FIELDS OF TABLE it_itab

FROM ( likp

INNER JOIN lips

ON lips_{vbeln = likp}vbeln

INNER JOIN vbup

ON vbup_{posnr = lips}posnr

and VBUP_{VBELN = LIPS}VBELN )

• left outer join VBAK

• on VBAK_{VBELN = LIPS}VGBEL

• inner join VBAP

• on VBAP_{VBELN = VBAK}VBELN )

WHERE likp~vbeln IN so_vbeln

AND likp~lifex IN so_lifex

AND likp~lfart IN so_lfart

AND likp~kunnr IN so_kunnr

AND likp~vstel IN so_vstel

AND likp~bldat IN so_bldat

AND likp~wadat_ist IN so_wadat

AND vbup~kosta IN so_kosta

AND vbup~wbsta IN so_wbsta

AND LIPS~LFIMG NE 0.

SELECT VBELN IHREZ INTO TABLE T_VBAK

FROM VBAK

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELN = IT_ITAB-VGBEL.

• APPEND T_VBAK.

• ENDSELECT.

SELECT VBELN MATNR POSNR INTO TABLE T_VBAP

FROM VBAP

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELN = IT_ITAB-VGBEL AND

MATNR = IT_ITAB-MATNR AND

POSNR = IT_ITAB-VGPOS.

• APPEND T_VBAP.

• ENDSELECT.

SELECT VBELV VBELN VBTYP_N INTO TABLE T_VBFA

FROM VBFA

FOR ALL ENTRIES IN IT_ITAB

WHERE VBELV = IT_ITAB-VBELN AND

VBTYP_N = 'M' .

SELECT KUNNR NAME1 INTO TABLE T_KNA1

FROM KNA1

FOR ALL ENTRIES IN IT_ITAB

WHERE KUNNR = IT_ITAB-KUNNR.

• APPEND T_KNA1.

• ENDSELECT.

SELECT MATNR MAKTX INTO TABLE T_MAKT

FROM MAKT

FOR ALL ENTRIES IN IT_ITAB

WHERE MATNR = IT_ITAB-MATNR.

• APPEND T_MAKT.

• ENDSELECT.

*Populate field with color attributes

LOOP AT it_itab INTO wa_ITAB.

• Populate color variable with colour properties

• Char 1 = C (This is a color property)

• Char 2 = 3 (Color codes: 1 - 7)

• Char 3 = Intensified on/off ( 1 or 0 )

• Char 4 = Inverse display on/off ( 1 or 0 )

• i.e. wa_ekko-line_color = 'C410'

REFRESH color.

colourize 'VBELN' 0. " .

WA_ITAB-farbe = color[].

ld_color = ld_color + 1.

• Only 7 colours so need to reset color value

IF ld_color = 3. "8

ld_color = 1.

ENDIF.

CONCATENATE 'C' ld_color '10' INTO wa_ITAB-line_color.

WA_ITAB-NAME1 = ''.

WA_ITAB-MAKTX = ''.

WA_ITAB-IHREZ = ''.

WA_ITAB-VBELV = ''.

READ TABLE T_KNA1 WITH KEY KUNNR = WA_ITAB-KUNNR.

IF SY-SUBRC = 0.

WA_ITAB-NAME1 = T_KNA1-NAME1.

ENDIF.

READ TABLE T_MAKT WITH KEY MATNR = WA_ITAB-MATNR.

IF SY-SUBRC = 0.

WA_ITAB-MAKTX = T_MAKT-MAKTX.

ENDIF.

READ TABLE T_VBAK WITH KEY VBELN = WA_ITAB-VGBEL.

IF SY-SUBRC = 0.

WA_ITAB-IHREZ = T_VBAK-IHREZ.

ENDIF.

READ TABLE T_VBFA WITH KEY VBELV = WA_ITAB-VBELN.

IF SY-SUBRC = 0.

WA_ITAB-VBELVA = T_VBFA-VBELN.

ENDIF.

• READ TABLE T_VBAP WITH KEY VBELN = WA_ITAB-VGBEL

• POSNR = WA_ITAB-VGPOS

• MATNR = WA_ITAB-MATNR.

• IF SY-SUBRC = 0.

• WA_ITAB-IHREZ = T_VBAK-IHREZ.

• ENDIF.

• wa_ekko-line_color = 'C410'.

MODIFY it_itab FROM wa_itab.

ENDLOOP.

ENDFORM. " data_retrieval

regards,

srinivas

<b>*reward for useful answers*</b>

[Former Member]

For all entries can replace the join statement.. in some cases let us say i have data in 2 internal tables and i consolidate that data into one internal table and now for say each record in this internal table there is one or more record in a database table then you can use for all entreis ...

It will just take the internal table values as input in the where condition and select the records accordingly.

[Former Member]

The main reason to use for all entries is that it provides some better performance than join or especially better than inner selects.

Instead of using joins or inner selects, try to use for all entries.

<b>Reward if helpful.</b>