urgent...how to use two tables..

[Former Member]

HI experts ..

I have a report where i am retriveing the data from table konv based on the data from the tables VBAP and VBAK..

and i have my select statment as

SELECT knumv

kposn

kschl

kbetr

FROM konv INTO TABLE it_konv

WHERE

knumv = vbak-knumv AND

kposn = vbap-posnr.

But i need to use FOR ALL ENTRIES. so can you please help me out how do i retrieve this data from KONV based on both the conditions. also i have already written seperate select queries for VBAP and VBAK.

[Former Member]

Hi,

Do it as follows:

SELECT knumv

kposn

kschl

kbetr

FROM konv INTO TABLE it_konv

FOR ALL ENTRIES IN it_vbapak

WHERE

*knumv = it_vbapak-knumv AND

kposn = it_vbapak-posnr.*

[Former Member]

Hi,

i am sending small code to u. check that one. i think u get an idea about that one.

u are retieve data from the konv table based on the primary key fields of vbak,vbap tables. but the three tables maintain a relation. at that time we should write the for all entries.

suppose we take two internal tables. in that internal tables field should be key fields. otherwise it shows as an performance issue.

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.

award points, if it is useful.

regards,

satish.

[Former Member]

HI ramesh..

Then what does this table it_vbapak contains.

>Shall i declare one more internal table it_vbapak

with only two fields in it like

data begin of it_vbapak

knumv like vbap-numv,

posnr like vbak-posnr

data end if it_vbapak

this is what you meant?

[Former Member]

Hi,

You have already specified as data was fetched from VBAK & VBAP. So the example was given thinking that the result is saved in a internal table IT_VBAPAK.

This contains the fields of VBAK & VBAP.

[former_member402443]

Hi Mona,

Check this code.

TYPES : BEGIN OF t_vbak,

vbeln LIKE vbak-vbeln,

knumv LIKE vbak-knumv,

posnr LIKE vbap-posnr,

END OF t_vbak.

TYPES : BEGIN OF t_konv,

knumv LIKE konv-knumv,

kposn LIKE konv-kposn,

kschl LIKE konv-kschl,

kbetr LIKE konv-kbetr,

END OF t_konv.

DATA : it_vbak TYPE STANDARD TABLE OF t_vbak WITH HEADER LINE,

it_konv TYPE STANDARD TABLE OF t_konv WITH HEADER LINE.

SELECT vbak_{vbeln vbak}knumv vbap_{posnr FROM vbak INNER JOIN vbap ON vbak}vbeln = vbap~vbeln INTO table it_vbak.

IF sy-subrc = 0.

SELECT knumv kposn kschl kbetr FROM konv

INTO TABLE it_konv FOR ALL ENTRIES IN

it_vbak WHERE knumv = it_vbak-knumv AND

kposn = it_vbak-posnr.

ENDIF.

Reward Points, if useful.

Regards,

Manoj Kumar

[Former Member]

Oops,

So many answers, You are extracting data based on VBAK (Sales Header) and VBAP (Sales Item).

- Make sure that document condition (KNUMV) available in table VBAP along with the line item for the given VBELN (SD number), better if you add new field KNUMV for I_VBAP (itab for VBAP).

And, use Now use the following.

SELECT knumv

kposn

kschl

kbetr

FROM konv

INTO CORRESPONDING FIELDS OF TABLE it_konv

WHERE knumv = vbap-knumv AND

kposn = vbap-posnr.

All the very best to you .

- Mohan.

[Former Member]

Thank you so much Mohan..

But can you explain me

Make sure that document condition (KNUMV) available in table VBAP along with the line item for the given VBELN (SD number), better if you add new field KNUMV for I_VBAP (itab for VBAP).

as you said above why cant we take vice versa.. i mean how do you decide which table to be taken first..any clue..

morever here according to you i am not seeing any join statement, so will this work..