topic Re: Select material Plant. in Application Development and Automation Discussions

Select material Plant.

Former Member — Tue, 11 Nov 2008 14:33:37 GMT

Hi All,

SELECT matnr werks

FROM marc INTO TABLE i1_marc where matnr IN s_matnr

AND werks in s_werks AND mmsta = '10'.

On doing this all the plant and material that is been fetched,

I need to determine if table MAST (Material to BOM Link) has an existing record for the same,

can any one help me in this?

Thanks:

Debrup.

Re: Select material Plant.

Former Member — Tue, 11 Nov 2008 14:36:38 GMT

Do a JOIN of tables.

Re: Select material Plant.

Former Member — Tue, 11 Nov 2008 14:36:51 GMT

Hi,

Please convert this Select into a Select based of Inner Join between MARD & MAST.

SELECT marc_{matnr marc}werks

INTO TABLE i1_marc

FROM marc inner join MAST

on marc-matnr = MAST-MAtnr and

marc-werks = MAST-werks

where marc~matnr IN s_matnr

AND marc_{werks in s_werks AND marc}mmsta = '10'.

Re: Select material Plant.

Former Member — Tue, 11 Nov 2008 14:37:46 GMT

Hi Debrup,

Then what you need to do it first fetch all the plants which are present in the table mast.

And then put a query in the marc table.


SELECT * FROM MAST INTO IT_MAST 
  WHERE WERKS = S_WERKS..
IF NOT MAST[] IS INITIAL.
SELECT matnr werks
FROM marc INTO TABLE i1_marc 
FOR ALL ENTRIES IN 
where matnr IN s_matnr
AND werks = IT_MAST
 AND mmsta = '10'.
ENDIF

Thanks,

Chidanand.

Re: Select material Plant.

Former Member — Tue, 11 Nov 2008 14:41:22 GMT

Take a SELECT on MAST table as

SELECT matnr werks

FROM mast INTO TABLE i1_mast where matnr IN s_matnr

AND werks in s_werks.

loop at it_marc.

read table it_mast with key matnr = it_marc-matnr and

werks = it_marc-werks.

if sy-subrc = 0.

*record exists in MAST table.

else

*record does not exist in MAST table.

endif.

This will help!!

Re: Select material Plant.

Former Member — Tue, 11 Nov 2008 14:46:21 GMT

Hi,

Why dont you do a select of MAST directly, you already have the material and the plant in that table. further you can join it with MARC to check mmsta.

As follows :



select * into corresponding fields of table table it_mast
from mast as m
inner join marc on as c
on m~matnr = c~matnr
and m~werks = m~werks
where m~matnr in s_matnr
and    m~werks in s_werks
and    c~mmsta = '10'.

regards,

Advait