For all entries ....

[Former Member]

Hello Everyone,

Wat is the code for the table join using <u>for all entries</u> statement?how do v join 6 tables using using the above statement...?

Thanking u in anticipation.

Regards,

Sirisha.

[Former Member]

let me give you small example for Vabk = header,vbap = line item tables.. and field vbeln = sales doc no being joining field ...

here it goes...

select kunnr vbeln erdat from vbak into table it_vbak

where vbeln in so_vbeln and

erdat in so_erdat.

if it_vbak[] is not initial.

select vbeln posnr from vbap into table it_vbap

for all entries in it_vbak

where vbeln = it_vbak-vbeln.

endif

[sunilachyut]

It would be better if you join 2/3 tables at once otherwise, it would end up forever to execute if its joining 6 tables.

hith

Sunil Achyut

[Former Member]

HI

GOOD

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

THANKS

MRUTYUN

[Former Member]

Siri,

check the below links

Regards,

Naveen

[aris_hidalgo]

Hi,

You can use the example above as your reference. The FOR ALL ENTRIES simply compares your select statement for the given records in an internal table. It will get those records where it will satisfy your where clause. Please note that you must have records in the internal table that your FOR ALL ENTRIES statement is comparing otherwise it will do a full table scan of the database table and thus will result in poor performance.

Hope this helps...

P.S. Please award points for useful answers.

[Former Member]

Hi,

Instead of joining 6 tables, You can create a view and then u can use this view in the select query.

This increases performance on your select query.

Thanks and Regards,

Harikrishna.

Dont forget to give points for helpful answers.