how to write this SQL

[Former Member]

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.

[Former Member]

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

[former_member129652]

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 = ' '.

[Former Member]

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