topic Re: Inner Join. in Application Development and Automation Discussions

Inner Join.

Former Member — Wed, 23 May 2007 15:53:44 GMT

I am using a Inner join statement. I dont see any problem in DEV but i see some problems in PMT. IT duplicating the records in the output. I see the same order twice.

The statement is,

SELECT plaf~plnum

mara_{normt plaf}matnr

makt~maktx

plaf_{pwwrk plaf}paart plaf~verid

plaf_{auffx plaf}psttr plaf_{gsmng plaf}plscn

INTO TABLE t_plaf

FROM plaf

INNER JOIN mara

ON plaf_{matnr EQ mara}matnr

INNER JOIN makt

ON mara_{matnr EQ makt}matnr

FOR ALL entries IN t_mara

WHERE plaf~matnr = t_mara-matnr.

However i looked into the MAKT table. I see 2 descriptions for the same material no.

I am assuming thats the problem.

ANy suggestions.

Ster.

Re: Inner Join.

Former Member — Wed, 23 May 2007 16:02:21 GMT

Hi,

Steps before using FOR ALL ENTRIES..

1) Make sure the driving internal table doesn't have any duplicate records..

In your case..

SORT t_mara by matnr.

DELETE ADJACENT DUPLICATES FROM T_MARA COMPARING MATNR.

2) Check if the internal table is not empty..


IF NOT t_mara[] IS INITIAL.

SELECT plaf~plnum
mara~normt plaf~matnr
makt~maktx 
plaf~pwwrk plaf~paart plaf~verid
plaf~auffx plaf~psttr plaf~gsmng plaf~plscn
INTO TABLE t_plaf
FROM plaf
INNER JOIN mara
ON plaf~matnr EQ mara~matnr
INNER JOIN makt 
ON mara~matnr EQ makt~matnr 
FOR ALL entries IN t_mara
WHERE plaf~matnr = t_mara-matnr
AND      makt~spras = sy-langu.              " Inserted this code..


ENDIF.

Thanks,

Naren

Re: Inner Join.

Former Member — Wed, 23 May 2007 16:06:46 GMT

Hello,

Check this code . I tried it now and it's working


Tables: plaf,mara,makt.

Data: it_plaf type standard table of plaf.
data: it_mara type standard table of mara,
      it_makt type standard table of makt.

select-options: s_plnum for plaf-plnum,
                s_matnr for mara-matnr.

Select * from plaf into table it_plaf
         where plnum in s_plnum
         and matnr in s_matnr.

if it_plaf[] is not initial.

   select * from mara into table it_mara
          for all entries in it_plaf
          where matnr = it_plaf-matnr.

  Select * from makt into table it_makt
          for all entries in it_plaf
          where matnr = it_plaf-matnr.

endif.

Loop at it_mara into wa_mara.
read table it_plaf into wa_plaf with key matnr = wa_mara-matnr.
if sy-subrc = 0.
move-corresponding to wa_final.
endif.
read table it_makt into wa_makt witk key matnr = wa_mara-matnr.
if sy-subrc = 0.
Move-corresponding to wa_final.
endif.
append wa_final to it_final.
clear: wa_mara,wa_plaf,wa_final.
endloop.

Regards,

Deepu.K

Re: Inner Join.

Former Member — Wed, 23 May 2007 16:10:16 GMT

> Hello,

> Check this code . I tried it now and it's working


> Tables: plaf,mara,makt.
> 
> Data: it_plaf type standard table of plaf.
> data: it_mara type standard table of mara,
>       it_makt type standard table of makt.
> ct-options: s_plnum for plaf-plnum,
>                 s_matnr for mara-matnr.
> plaf into table it_plaf
>          where plnum in s_plnum
> and matnr in s_matnr.
> 
> if it_plaf[] is not initial.
> 
>    select * from mara into table it_mara
>        for all entries in it_plaf
>    where matnr = it_plaf-matnr.
>  Select * from makt into table it_makt
>          for all entries in it_plaf
>  where matnr = it_plaf-matnr.
> 
> endif.
> 
> Loop at it_mara into wa_mara.
> read table it_plaf into wa_plaf with key matnr =
> wa_mara-matnr.
> if sy-subrc = 0.
> move-corresponding to wa_final.
> endif.
> read table it_makt into wa_makt witk key matnr =
> wa_mara-matnr.
> if sy-subrc = 0.
> Move-corresponding to wa_final.
> endif.
> append wa_final to it_final.
> clear: wa_mara,wa_plaf,wa_final.
> endloop.
>

> Regards,

> Deepu.K

It would be better for performance to use Field-Symbols -;)


FIELD-SYMBOLS: <MARA> LIKE LINE OF IT_MARA.

Loop at it_mara into <MARA>.
*Process data....
endloop.

Greetings,

Blag.

Re: Inner Join.

Former Member — Wed, 23 May 2007 16:26:30 GMT

Thanks everyone.

Ster.