Need simple and effecient select.

[Former Member]

Hi ,

Iam using the select as below ,

Select MATNR from MARA into l_temp_MATNR where

MATNR = I_stag_tableZZITEM_CODE.

MTART = 'ZHAB'.

Append l_temp_MATNR

Endloop.

Loop at l_temp_MATNR .

Select MATNR from MARC into l_temp_MATNR where

MATNR = l_temp_MATNR -MATNR

Endloop.

Can any one combine it into join condtion ? wether it is possible .

Regards,

Sri

[Former Member]

Hi ,

Iam using the select as below ,

Select MATNR from MARA into l_temp_MATNR where

MATNR = I_stag_tableZZITEM_CODE.

MTART = 'ZHAB'.

Append l_temp_MATNR

Endloop.

Loop at l_temp_MATNR .

Select MATNR from MARC into l_temp_MATNR where

MATNR = l_temp_MATNR -MATNR

Endloop.

Can any one combine it into join condtion ? wether it is possible .

Regards,

Sri

[Former Member]

Hi,

Try this

Declare i_matnr type mara-matnr.

Select MATNR from MARA into table l_MATNR where

MATNR = I_stag_table-ZZITEM_CODE and

MTART = 'ZHAB'.

if i_matnr is not initial.

Select MATNR from MARC into l_temp_MATNR for all entries in i_matnr

where

MATNR = l_MATNR -MATNR

endif.

Regards

Krishna

[Former Member]

i dont know why do you want to use two selects here.

I would suggest you to go for a join on mara and marc

and also include plant in your internal table, so that you are easily able to identify the material belongs to which plant. Or use select with distinct.

select distinct matnr
from mara as a join marc as b
into table i_tab
on a~matnr = b~matnr
where a~matnr = I_stag_table-ZZITEM_CODE and
          a~mtart = 'ZHAB'. " I'd prefer not to hardcode the material type here.

Regards,

Abdullah

Edited by: ZAFCO ABAP on May 6, 2009 6:01 PM

[Former Member]

Why don't you try writing the JOIN yourself and get back to the forum if you have a problem?

Hmmmm.... now that I look at it a bit more closely, I don't see any logic behind what you are trying to do (retrieve MATNR knowing MATNR). Some other issues as well.

So why don't you refine your logic and then get back to the forum?

Rob

Edited by: Rob Burbank on May 6, 2009 10:06 AM

[Former Member]

select t2~matnr
   from mara as t1 innerjoin marc as t2
   on t1~matnr = t2~matnr
   into table <itab>
   where t1~matnr = I_stag_tableZZITEM_CODE
       and t1~mtart =  'ZHAB'.

Thanks

Satya

[Former Member]

Hi Sri,

I wrote the Join condition , please check that --

SELECT marc~matnr
       FROM mara INNER JOIN marc
       ON mara~matnr = marc~matnr
         WHERE mara~matnr = i_stag_table-zzitem_code AND
               mara~mtart = 'ZHAB'
         INTO TABLE  l_temp_matnr.

Hope this will help you.

Regards

Pinaki