select all entires

[Former Member]

hi

i am abap learner, can anyone help me where should we use "select all entires" and why?

thank u

[Former Member]

I guess you mean SELECT FOR ALL ENTRIES.

It's better to use a database join.

Rob

[abdulazeez12]

Hi

Welcome to SDN forums!

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,

shakir

*reward for useful answers*

[Former Member]

assuming that 'select all entries' corresponds to 'select *' following are few cases where you can use select *

- when you need to fetch and access all raher 80% of the fields of perticular table

- when you need to use large number of fields (e.g 20-30) from a table

[former_member223537]

Hi Alka,

If you want to select data from a table which is based on content of previous table, then you need to use FOR ALL ENTRIES.

eg.

VBAK - Header data & VBAP - Line item data.

select * from VBAK into i_vbak where ...

if not i_vbak is initial.

sort i_vbak by vbeln.

select * from VBAP

into i_vbap

for all entries in i_vbak

where vbeln = i_vbak-vbeln.

endif.

Here we are retrieving data from VBAP only for those VBELN which exist in VBAK.

Say VBAK has 10 vbeln i.e. 10 records.... so we are retrieving data from VBAP only for these 10 vbeln.

Best regards,

Prashant

[Former Member]

Use FOR ALL ENTRIES if you want to join it with another table in the database.

If you are used in joining two tables like FROM table1 INNER JOIN table 2 ON f1=f2.

FOR ALL ENTRIES is like FROM table1 INNER JOIN internal_table ON f1 = f2.

It's just an example for you to understand, but the code is different in real life situation.

[abdulazeez12]

Hi Alka-

Please close the thread if you are satisfied with the answers.

[Former Member]

hi

thanks shakir and prashanth