Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

For all entries ....

Former Member
0 Kudos

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.

6 REPLIES 6

Former Member
0 Kudos

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
Contributor
0 Kudos

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
0 Kudos

HI

GOOD

SELECT likpvbeln likplifex likpbldat likpwadat likpwadat_ist likpkodat likp~lfart

likpkunnr likpvstel lipsposnv lipslfimg lipsvrkme lipslgmng lips~meins

lipswerks lipslgort lipscharg lipsvbelv lipsposnr lipsmatnr

lipsvbeln LIPSVGBEL LIPSVGPOS vbupkosta vbupwbsta vbupposnr vbup~vbeln

  • VBAKIHREZ VBAKVBELN VBAP~VBELN

INTO CORRESPONDING FIELDS OF TABLE it_itab

FROM ( likp

INNER JOIN lips

ON lipsvbeln = likpvbeln

INNER JOIN vbup

ON vbupposnr = lipsposnr

and VBUPVBELN = LIPSVBELN )

  • left outer join VBAK

  • on VBAKVBELN = LIPSVGBEL

  • inner join VBAP

  • on VBAPVBELN = VBAKVBELN )

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
0 Kudos

Siri,

check the below links

Regards,

Naveen

aris_hidalgo
Contributor
0 Kudos

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
0 Kudos

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.