topic Re: Alternative for Inner Join in Application Development and Automation Discussions

Alternative for Inner Join

Former Member — Fri, 30 May 2008 15:05:30 GMT

Hi all,

Im extracting different fields from material master data into different internal tables. I need to have all the data in these internal table in one final internal table, I don't want to use inner join to get the data into the final internal table. Can you suggest any alternative for this?

Thanks.

Re: Alternative for Inner Join

Former Member — Fri, 30 May 2008 15:10:54 GMT

Hello,

Use the FOR ALL ENTRIES IN and then mount the final table using loop and read table commands.


PARAMETERS p_city TYPE spfli-cityfrom. 

TYPES: BEGIN OF entry_tab_type, 
         carrid TYPE spfli-carrid, 
         connid TYPE spfli-connid, 
       END OF entry_tab_type. 

DATA: entry_tab   TYPE TABLE OF entry_tab_type, 
      sflight_tab TYPE SORTED TABLE OF sflight 
                       WITH UNIQUE KEY carrid connid fldate. 

SELECT carrid connid 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF TABLE entry_tab 
       WHERE cityfrom = p_city. 

SELECT carrid connid fldate 
       FROM sflight 
       INTO CORRESPONDING FIELDS OF TABLE sflight_tab 
       FOR ALL ENTRIES IN entry_tab 
       WHERE carrid = entry_tab-carrid AND 
             connid = entry_tab-connid.

Regards.

Re: Alternative for Inner Join

Former Member — Fri, 30 May 2008 15:11:20 GMT

Hi,

YOu can try to loop on one table and read data from other table s and modify table based on the read data.

some thing like this :



loop at it_mara.
  read table it_marc with key matnr = it_mara-matnr.
  if sy-subrc = 0.
 modify table it_marc index sy-tabix transporting werks.

In the above code we are updating werks (plant) from it_marc to it_mara which is our main table.

Regards,

Himanshu Verma

Re: Alternative for Inner Join

Former Member — Fri, 30 May 2008 15:35:55 GMT

hi check this..this is the alternative for the joins..

REPORT ZZZZ000000.

tables:mara,marc,mard,makt.

data:begin of it_mara occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

end of it_mara.

data:begin of it_marc occurs 0,

matnr like marc-matnr,

pstat like marc-pstat,

werks like marc-werks,

end of it_marc.

data:begin of it_mard occurs 0,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of it_mard.

data:begin of it_final occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

pstat like marc-pstat,

werks like marc-werks,

lgort like mard-lgort,

labst like mard-labst,

maktx like makt-maktx,

end of it_final.

select-options:s_matnr for mara-matnr.

select matnr

mtart

meins

from mara

into table it_mara

where matnr in s_matnr.

if not it_mara[] is initial.

select matnr

pstat

werks

from marc

into table it_marc

for all entries in it_mara

where matnr = it_mara-matnr.

if not it_marc[] is initial.

select werks

lgort

labst

from mard

into table it_mard

for all entries in it_marc

where werks = it_marc-werks.

endif.

loop at it_mara.

it_final-matnr = it_mara-matnr.

it_final-mtart = it_mara-mtart.

it_final-meins = it_mara-meins.

read table it_marc with key matnr = it_mara-matnr.

it_final-werks = it_marc-werks.

it_final-pstat = it_marc-pstat.

read table it_mard with key werks = it_marc-werks.

it_final-lgort = it_mard-lgort.

it_final-labst = it_mard-labst.

if sy-subrc = 0.

select maktx from makt into it_final-maktx where matnr = it_final-matnr.

endselect.

endif.

append it_final.

endloop.

loop at it_final.

write:/ it_final-matnr under 'material',

it_final-mtart under 'material type',

it_final-meins under 'unit of measure',

it_final-werks under 'plant' ,

it_final-pstat under 'status',

it_final-lgort under 'storage loc',

it_final-labst under 'stock',

it_final-maktx.

endloop.

regards,

venkat