Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Inner Join.

Former Member
0 Kudos
162

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

maranormt plafmatnr

makt~maktx

plafpwwrk plafpaart plaf~verid

plafauffx plafpsttr plafgsmng plafplscn

INTO TABLE t_plaf

FROM plaf

INNER JOIN mara

ON plafmatnr EQ maramatnr

INNER JOIN makt

ON maramatnr EQ maktmatnr

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos
121

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

4 REPLIES 4

Former Member
0 Kudos
122

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

0 Kudos
121

Thanks everyone.

Ster.

Former Member
0 Kudos
121

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

0 Kudos
121

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