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: 

how to select mchb & mara

Former Member
0 Kudos
523

Hi,

I have select-options: s_matnr for mchb-matnr &

s_pradh for mara-pradh.

I want to select records from mchb(matnr,wekrs,lgort)

and mara(matnr,pradh).

can any one tell me how to i achieve this?

points guaranteed..

do i need to write join statement?

cheers

kaki

1 ACCEPTED SOLUTION

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos
153

Yep, do a join.



report zrich_0001 .


tables: mchb, mara.

data: begin of itab occurs 0,
      matnr type mchb-matnr,
      werks type mchb-werks,
      lgort type mchb-lgort,
      pradh type mara-prdha,
      end of itab.


select-options: s_matnr for mchb-matnr,
                s_prdha for mara-prdha.


start-of-selection.


  select * into corresponding fields of table itab
            from mchb
                  inner join mara
                      on mchb~matnr = mara~matnr
                              where mchb~matnr in s_matnr
                                and mara~prdha in s_prdha.

Regards,

Rich Heilman

6 REPLIES 6

manuel_bassani
Contributor
0 Kudos
153

Hi,

select cmatnr cwerks clgort aprdha

from mara as a join mchb as c on cmatnr = amatnr

into table ......

where a~matnr in s_matnr

and a~pradh in s_pradh

group by cmatnr cwerks c~lgort

regards, Manuel

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos
154

Yep, do a join.



report zrich_0001 .


tables: mchb, mara.

data: begin of itab occurs 0,
      matnr type mchb-matnr,
      werks type mchb-werks,
      lgort type mchb-lgort,
      pradh type mara-prdha,
      end of itab.


select-options: s_matnr for mchb-matnr,
                s_prdha for mara-prdha.


start-of-selection.


  select * into corresponding fields of table itab
            from mchb
                  inner join mara
                      on mchb~matnr = mara~matnr
                              where mchb~matnr in s_matnr
                                and mara~prdha in s_prdha.

Regards,

Rich Heilman

Former Member
0 Kudos
153

SELECT a~matnr

a~pradh

b~werks

b~lgort

INTO TABLE I_PROD

FROM ( mara AS a

JOIN machb AS b

ON amatnr = bmatnr )

where a~matnr = S_Matnr --> selection

and b~werks = S_werks

and b~lgort = S_LGORT.

0 Kudos
153

Hi all

Thanks for all replies

kaki

0 Kudos
153

Please make sure to award points for helpful answers and mark you post as solved if your problem is solved. Thanks.

Regards,

Rich Heilman

Former Member
0 Kudos
153

Hi,

You can use the Rich Heilman's solution with slight changes, instead of selecting all the fields select only the required fields and also in where clause restrict matnr from mara itself along with the product hierarchy.

report zrich_0001 .

tables: mchb, mara.

data: begin of itab occurs 0,

matnr type mchb-matnr,

werks type mchb-werks,

lgort type mchb-lgort,

pradh type mara-prdha,

end of itab.

select-options: s_matnr for mchb-matnr,

s_prdha for mara-prdha.

start-of-selection.

select mchbmatnr mchbwerks mchblgort maraprdha

into table itab

from mchb

inner join mara

on mchbmatnr = maramatnr

where mara~matnr in s_matnr

and mara~prdha in s_prdha.

Thanks

Giridhar