topic Re: regaring select statement in Application Development and Automation Discussions

regaring select statement

Former Member — Tue, 29 Nov 2005 18:28:30 GMT

hello every one give me the answer for all the tables which iam showing using for all entries and the answer should be in the final out put internal table iam attaching the code also as well. iam not getting the result for all the tables only three iam getting try to give some good code and make me happy iam trying this from past ten days help me

thank u .

&----

*& Report ZTABLEREPORT2 *

*& *

&----

*& *

&----

REPORT ZTABLEREPORT2 NO STANDARD PAGE HEADING

LINE-SIZE 250

LINE-COUNT 22(3).

TABLES : MARA,

MAKT,

MARC,

VBAP,

EKKO,

EKPO,

KNA1.

DATA : BEGIN OF T_MARA OCCURS 0,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

MEINS LIKE MARA-MEINS,

END OF T_MARA.

DATA : BEGIN OF T_MAKT OCCURS 0,

MATNR LIKE MAKT-MATNR,

MAKTX LIKE MAKT-MAKTX,

SPRAS LIKE MAKT-SPRAS,

END OF T_MAKT.

DATA : BEGIN OF T_MARC OCCURS 0,

MATNR LIKE MARC-MATNR,

WERKS LIKE MARC-WERKS,

END OF T_MARC.

DATA : BEGIN OF T_KNA1 OCCURS 0,

KUNNR LIKE KNA1-KUNNR,

NAME1 LIKE KNA1-NAME1,

LAND1 LIKE KNA1-LAND1,

END OF T_KNA1.

DATA : BEGIN OF T_VBAP OCCURS 0,

MATNR LIKE VBAP-MATNR,

POSNR LIKE VBAP-POSNR,

MATKL LIKE VBAP-MATKL,

VBELN LIKE VBAP-VBELN,

END OF T_VBAP.

DATA : BEGIN OF T_EKPO OCCURS 0,

EBELN LIKE EKPO-EBELN,

EBELP LIKE EKPO-EBELP,

BUKRS LIKE EKPO-BUKRS,

WERKS LIKE EKPO-WERKS,

LGORT LIKE EKPO-LGORT,

MATNR LIKE EKPO-MATNR,

MANDT LIKE EKPO-MANDT,

END OF T_EKPO.

DATA : BEGIN OF T_FINAL OCCURS 0,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

MEINS LIKE MARA-MEINS,

WERKS LIKE MARC-WERKS,

MAKTX LIKE MAKT-MAKTX,

SPRAS LIKE MAKT-SPRAS,

VBELN LIKE VBAP-VBELN,

POSNR LIKE VBAP-POSNR,

MATKL LIKE VBAP-MATKL,

EBELN LIKE EKPO-EBELN,

EBELP LIKE EKPO-EBELP,

BUKRS LIKE EKPO-BUKRS,

KUNNR LIKE KNA1-KUNNR,

LAND1 LIKE KNA1-LAND1,

NAME1 LIKE KNA1-NAME1,

LGORT LIKE EKPO-LGORT,

END OF T_FINAL.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_BUKRS FOR EKPO-BUKRS,

S_KUNNR FOR KNA1-KUNNR,

S_WERKS FOR MARC-WERKS,

S_MATNR FOR MARA-MATNR.

SELECTION-SCREEN END OF BLOCK B1.

START-OF-SELECTION.

SELECT MATNR mtart meins

FROM MARA INTO CORRESPONDING FIELDS OF TABLE T_MARA

WHERE MATNR IN S_MATNR.

IF SY-SUBRC <> 0.

WRITE : / 'NO RECORDS FOUND'.

EXIT.

ENDIF.

SELECT MATNR WERKS

FROM MARC

INTO CORRESPONDING FIELDS OF TABLE T_MARC

FOR ALL ENTRIES IN T_MARA

WHERE MATNR = T_MARA-MATNR

and werks in s_werks.

SORT t_mara BY matnr.

SORT t_marc BY matnr.

LOOP AT T_MARA.

MOVE T_MARA-matnr TO T_FINAL-matnr.

move t_mara-mtart to t_final-mtart.

move t_mara-meins to t_final-meins.

loop at t_marc where matnr eq t_mara-matnr.

move t_marc-werks to t_final-werks.

endloop.

select matnr maktx spras

from makt

into corresponding fields of table t_makt

for all entries in t_mara

where matnr = t_mara-matnr

and spras = sy-langu.

loop at t_makt.

move t_makt-maktx to t_final-maktx.

move t_makt-spras to t_final-spras.

endloop.

append t_final.

endloop.

SELECT SINGLE posnr matkl vbeln FROM vbap
INTO (T_FINAL-MAtkl,T_FINAL-vbeln,t_final-posnr)
WHERE MATNR = T_MARA-MATNR.

select matnr posnr vbeln matkl
from vbap
into corresponding fields of table t_vbap
for all entries in t_mara
where matnr = t_mara-matnr
and posnr = ''.

loop at t_final.

write : t_final-matnr,

5 t_final-mtart,

15 t_final-meins,

25 t_final-werks,

40 t_final-maktx,

65 t_final-spras,

80 t_final-posnr,

95 t_final-matkl,

110 t_final-vbeln.

endloop.

loop at t_vbap.
move t_vbap-posnr to t_final-posnr.
move t_vbap-vbeln to t_final-vbeln.
move t_vbap-matkl to t_final-matkl.

READ TABLE T_MARC WITH KEY MATNR = T_MARA-MATNR.
WERKS = T_MARC-WERKS.
LOOP AT T_MARD WHERE MATNR EQ T_MARC-MATNR.
MOVE T_MARC-WERKS TO T_FINAL-WERKS.
APPEND t_FINAL.
CLEAR t_FINAL.

SELECT single POSNR MATKL VBELN
FROM VBAP
INTO CORRESPONDING FIELDS OF T_vbap
WHERE MATNR = T_MARA-MATNR.

*if sy-subrc eq 0.

move-corresponding t_vbap to t_final.

else.
continue.
endif.

endloop.

SELECT SINGLE MAKTX SPRAS FROM MAKT
INTO (T_FINAL-MAKTX,T_FINAL-SPRAS)
WHERE MATNR = T_MARA-MATNR
AND SPRAS = SY-LANGU.

SELECT POSNR MATKL VBELN
FROM VBAP
INTO CORRESPONDING FIELDS OF TABLE T_FINAL
WHERE MATNR = T_MARA-MATNR.

select vbeln posnr
from vbap
into table t_final
where matnr = t_mara-matnr.

SELECT KUNNR NAME1 LAND1
APPENDING TABLE T_FINAL
FROM KNA1
WHERE WERKS = T_MARC-WERKS.

WRITE : / T_FINAL-MATNR, 15 T_FINAL-MTART,
30 T_FINAL-MEINS, 45 T_FINAL-MAKTX, 80 T_FINAL-SPRAS,
95 T_FINAL-POSNR, 110 T_FINAL-MATKL, 120 T_FINAL-VBELN,
140 t_final-maktx, 165 t_final-spras, 180 t_final-vbeln,
200 t_final-posnr, 210 t_final-kunnr, 225 t_final-name1,
245 t_final-land1.

LOOP AT T_MARC WHERE MATNR EQ T_MARA-MATNR.

MOVE T_MARC TO T_FINAL.
APPEND T_FINAL.

CLEAR T_FINAL.

ENDLOOP.

LOOP AT T_FINAL.
WRITE :70 T_MARC-WERKS.
ENDLOOP.
endloop.

*SELECT KUNNR NAME1 LAND1

APPENDING TABLE T_FINAL
FROM KNA1
WHERE WERKS = T_MARC-WERKS.

ENDSELECT.
LOOP AT T_KNA1.
WRITE : 180 T_FINAL-KUNNR, 195 T_FINAL-NAME1,
210 T_FINAL-LAND1.

Re: regaring select statement

RichHeilman — Tue, 29 Nov 2005 18:38:57 GMT

Ok, you are getting MARA, MARC, and MAKT correctly, you need VBAP as well? What else?

Regards,

Rich HEilman

Re: regaring select statement

Former Member — Tue, 29 Nov 2005 18:42:15 GMT

Please close the other post you created. It is not desirable to see multiple posts created for the same issue. Did the responses there help you? If so, reward them and close.

Thanks,

Srinivas

Re: regaring select statement

Former Member — Tue, 29 Nov 2005 19:52:32 GMT

Hope this helps. Please note that the join will not retreive all the records from MARA if the material has no description in Makt table.

START-OF-SELECTION.

select a~matnr

a~mtart

a~meins

b~werks

d~maktx

c~lgort

into corresponding fields of table t_final

from mara as a

inner join marc as b

on a_{matnr = b}matnr

inner join mard as c

on b_{werks = c}werks and

a_{matnr = c}matnr

inner join makt as d

on d_{matnr = a}matnr

where a~matnr in s_matnr and

b~werks in s_werks and

d~spras = sy-langu.

IF SY-SUBRC <> 0.

WRITE : / 'NO RECORDS FOUND'.

EXIT.

ENDIF.

sort t_final by matnr.

SELECT vbeln POSNR matnr MATKL

FROM VBAP

INTO CORRESPONDING FIELDS OF TABLE T_vbap

WHERE MATNR in s_matnr.

SELECT werks KUNNR LAND1 NAME1

INTO CORRESPONDING FIELDS OF TABLE T_KNA1

FROM KNA1

WHERE WERKS in s_werks.

end-of-selection.

if t_final[] is initial.

write:/ 'No records exists for the selection criteria'.

else.

loop at t_final.

WRITE : / T_FINAL-MATNR, 15 T_FINAL-MTART,

30 T_FINAL-MEINS, 45 T_FINAL-MAKTX, 80 sy-langu,

140 t_final-maktx, 165 sy-langu.

clear t_vbap.

read table T_vbap with key matnr = t_final-matnr.

if sy-subrc = 0.

Write: 120 t_vbap-vbeln , 95 t_vbap-POSNR , 110 t_vbap-MATKL,

180 t_final-vbeln.

endif.

read table T_KNA1 with key werks = t_final-werks.

if sy-subrc = 0.

write: 210 t_final-kunnr, 225 t_final-name1,

245 t_final-land1.

endif.

endloop.

endif.

Re: regaring select statement

Former Member — Tue, 29 Nov 2005 20:35:01 GMT

Hi Girish,

Please award and close the post if the solution is helpful.

Re: regaring select statement

Former Member — Fri, 02 Dec 2005 19:25:36 GMT

Hi Girish,

This code may help u, check it out.

&----

*& Report YRAMU_SDN *

*& *

&----

*& *

&----

report yramu_sdn no standard page heading

line-size 250

line-count 20(3).

*TABLES

tables : mara,

makt,

marc,

vbap,

ekpo.

*INTERNAL TABLES

data : begin of t_mara occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

end of t_mara.

data : begin of t_makt occurs 0,

matnr like makt-matnr,

maktx like makt-maktx,

spras like makt-spras,

end of t_makt.

data : begin of t_marc occurs 0,

matnr like marc-matnr,

werks like marc-werks,

end of t_marc.

data : begin of t_vbap occurs 0,

vbeln like vbap-vbeln,

posnr like vbap-posnr,

matnr like vbap-matnr,

matkl like vbap-matkl,

end of t_vbap.

data : begin of t_ekpo occurs 0,

ebeln like ekpo-ebeln,

ebelp like ekpo-ebelp,

matnr like ekpo-matnr,

bukrs like ekpo-bukrs,

lgort like ekpo-lgort,

end of t_ekpo.

data : begin of t_final occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

werks like marc-werks,

maktx like makt-maktx,

spras like makt-spras,

vbeln like vbap-vbeln,

posnr like vbap-posnr,

matkl like vbap-matkl,

ebeln like ekpo-ebeln,

ebelp like ekpo-ebelp,

bukrs like ekpo-bukrs,

lgort like ekpo-lgort,

end of t_final.

*SELECTION SCREEN

selection-screen begin of block b1 with frame title text-001.

select-options : s_matnr for mara-matnr,

s_werks for marc-werks,

s_bukrs for ekpo-bukrs.

selection-screen end of block b1.

*START OF SELECTION

start-of-selection.

select matnr

mtart

meins

from mara

into table t_mara

where matnr in s_matnr.

if sy-subrc <> 0.

write : / 'NO RECORDS FOUND'.

exit.

endif.

select matnr

werks

from marc

into table t_marc

for all entries in t_mara

where matnr = t_mara-matnr

and werks in s_werks.

select matnr

maktx

spras

from makt

into table t_makt

for all entries in t_mara

where matnr = t_mara-matnr

and spras = sy-langu.

select vbeln

posnr

matnr

matkl

from vbap

into table t_vbap

for all entries in t_mara

where matnr = t_mara-matnr.

select ebeln

ebelp

matnr

bukrs

lgort

from ekpo

into table t_ekpo

for all entries in t_mara

where matnr = t_mara-matnr

and bukrs in s_bukrs.

sort : t_mara by matnr,

t_marc by matnr,

t_makt by matnr,

t_vbap by matnr,

t_ekpo by matnr.

loop at t_mara.

read table t_marc with key matnr = t_mara-matnr binary search.

read table t_makt with key matnr = t_mara-matnr binary search.

read table t_vbap with key matnr = t_mara-matnr binary search.

read table t_ekpo with key matnr = t_mara-matnr binary search.

move : t_mara-matnr to t_final-matnr,

t_mara-mtart to t_final-mtart,

t_mara-meins to t_final-meins,

t_marc-werks to t_final-werks,

t_makt-maktx to t_final-maktx,

t_makt-spras to t_final-spras,

t_vbap-vbeln to t_final-vbeln,

t_vbap-posnr to t_final-posnr,

t_vbap-matkl to t_final-matkl,

t_ekpo-ebeln to t_final-ebeln,

t_ekpo-ebelp to t_final-ebelp,

t_ekpo-bukrs to t_final-bukrs,

t_ekpo-lgort to t_final-lgort.

append : t_final.

clear : t_final.

endloop.

*END OF SELECTION

end-of-selection.

loop at t_final.

write : / sy-vline,

t_final-matnr,

sy-vline,

t_final-mtart,

sy-vline,

t_final-meins,

sy-vline,

t_final-werks,

sy-vline,

t_final-maktx,

sy-vline,

t_final-spras,

sy-vline,

t_final-vbeln,

sy-vline,

t_final-posnr,

sy-vline,

t_final-matkl,

sy-vline,

t_final-ebeln,

sy-vline,

t_final-ebelp,

sy-vline,

t_final-bukrs,

sy-vline,

t_final-lgort,

sy-vline.

endloop.

write : / sy-uline.

*END OF PAGE

end-of-page.

write : / sy-uline(159).

*TOP OF PAGE

top-of-page.

write : / sy-uline(159).