fetching records from more than 3 standard table

[Former Member]

hi experts,

i have a requirement

that i have to select the fields vbeln, netwr, kunrg from vbrk table,

vbeln from vbak,

matnr from vbap,

maktx from makt,

name1 from kna1.

based on the condition

taking billing date and customer group in selection screen as select options.

help me the select query.

thanks

krithi

[Former Member]

First retrieve data from VBRK. based on document number vbeln retrieve material and customer from VAPMA table (which is index table for sales order item). from VAPMA KUNNR get name1 from kna1.

[Former Member]

You can add your WHERE clause

TABLES: vbak, vbap, makt, kna1.

DATA:
  BEGIN OF myrec,
    vbeln       LIKE vbak-vbeln,
    matnr       LIKE vbap-matnr,
    maktx       LIKE makt-maktx,
    name1       LIKE kna1-name1,
  END OF myrec,
  it_stuff LIKE STANDARD TABLE OF myrec.

SELECT a~vbeln b~matnr c~maktx d~name1
  INTO CORRESPONDING FIELDS OF TABLE it_stuff
  FROM vbak AS a
  JOIN vbap AS b ON a~vbeln EQ b~vbeln
  JOIN makt AS c ON b~matnr EQ c~matnr
  JOIN kna1 AS d ON a~kunnr EQ d~kunnr.

[Former Member]

hi ,

tables: vbak,vbap,makt,kna1,vbrk.

data: begin of itab occurs 0,

vbeln like vbrk-vbeln,

netwr like vbrk-netwr,

knurg like vbrk-kunrg,

KDGRP like vbrk-KDGRP,

FKDAT like vbrk-FKDAT,

end of itab.

data: begin of itab1 occurs 0,

matnr like vbap-matnr,

vbeln like vbap-vbeln,

end of itab1 .

data: begin of itab2 occurs 0,

matnr like makt-matnr,

maktx like makt-maktx,

end of itab2 .

data: begin of final occurs 0,

vbeln like vbrk-vbeln,

netwr like vbrk-netwr,

knurg like vbrk-kunrg,

matnr like vbap-matnr,

maktx like makt-maktx,

name1 like kna1-name1,

end of final.

data: v_matnr like vbap-matnr,

v_maktx like makt-maktx.

select-options:s_date for sy-datum,

s_KDGRP for vbrk-KDGRP.

start-of-selection .

select

vbeln

kunrg

netwr

KDGRP

FKDAT

from vbrk

into corresponding fields of table itab

where fkdat in s_date

and KDGRP in s_KDGRP.

break-point .

select matnr vbeln

from vbap

into table itab1

for all entries in itab

where vbeln = itab-vbeln .

select matnr maktx

from makt

into table itab2

for all entries in itab1

where matnr = itab1-matnr

and spras = 'EN'.

end-of-selection.

loop at itab.

final-vbeln = itab-vbeln.

final-knurg = itab-knurg.

final-netwr = itab-netwr.

read table itab1 with key vbeln = itab-vbeln.

final-matnr = itab1-matnr.

read table itab2 with key matnr = itab1-matnr.

final-maktx = itab2-maktx.

append final.

endloop.

loop at final.

write:/

final-vbeln ,

final-knurg ,

final-netwr ,

final-matnr ,

final-maktx .

endloop.

regards,

venkat.