topic how to write this SQL in Application Development and Automation Discussions

how to write this SQL

Former Member — Tue, 17 Jul 2012 17:20:17 GMT

Hi experts,

I want to download the item#/site#/stock of all active items. I used the following SQL, and it runs fast(in 2 minutes) even thought MARC and MARD are very very big.

Select P~matnr

P~werks

F~labst

INTO CORRESPONDING FIELDS OF TABLE git_mard

FROM MARC as p

inner join mard as f on p~matnr = f~matnr and

p~werks = f~werks

where p~matnr in s_matnr and

p~werks in s_werks and

p~mmsta = ' '.

but now I need to download the item even though table MARD doesn't have item#/site# record. if I don't find record in MARD by item#/site#, just put stock=0.

I tried the following SQL , but it will take me 15 minutes and more.

select matnr werks from marc INTO TABLE git_marc where mmsta = ' '.

select matnr werks labst from mard into table git_mard for all entries in git_marc
where matnr = git_marc-matnr and werks = git_marc-werks.

LOOP AT git_marc into GWA_Marc.
read table GIT_mard into GWA_mard with key matnr = GWA_marc-matnr werks = GWA_marc-werks.

......

ENDLOOP.

How to let my SQL fast? Thanks.

Re: how to write this SQL

Former Member — Tue, 17 Jul 2012 17:33:08 GMT

select matnr werks from marc INTO TABLE git_marc where mmsta = ' '

1.Since sap has provided a index on WERKS for MARC i would select all plants first if not specified on selection screen and then use the above select query with werks in s_werks.

2. i would also filter on LVORM (Flag Material for Deletion at Plant Level).

1.if git_marc is not initial

select matnr werks labst from mard into table git_mard for all entries in git_marc
where matnr = git_marc-matnr and werks = git_marc-werks.

endif.

2. i would also take LGORT in picture while selecting MARD

3. To read i would sort and use binary search or use hashed type itab and read it

Re: how to write this SQL

former_member129652 — Wed, 18 Jul 2012 03:09:49 GMT

Hi,

Isn't your requirement a typical left outer join?

Select P~matnr P~werks F~labst

INTO CORRESPONDING FIELDS OF TABLE git_mard

FROM MARC as p

left join mard as f on p~matnr = f~matnr

and p~werks = f~werks

where p~matnr in s_matnr and

p~werks in s_werks and

p~mmsta = ' '.

Re: how to write this SQL

Former Member — Wed, 18 Jul 2012 05:29:03 GMT

Hi Michael,

I think as Michael Lee says you will get your requirement by Left outer join. You will get all the item records which are actually present in both the table. But because of left outer join you will get some more items which have entry in first table and not in second table.

When you will retrive all the records in internal table then applying loop to internal table you can add value zero to stock column where stock will be blank. And subsequently you can modify internal table.

Thanks

Santosh