topic Re: Joining 3 Tables in Application Development and Automation Discussions

Joining 3 Tables

Former Member — Tue, 08 Jul 2008 04:54:43 GMT

Hi all,

My requirement is to joining 3 table i.e. mara,stpo,mbew .

The fields are matnr mtart ,extwg ,verpr, idnrk(domain is stpo-matnr)

Please give me the code for the same.

Please gimme the logic for the same.

Points will be rewarded .

Thanks in advance,

Supriya.

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:06:54 GMT

Hi Supriya,

Better you take the data from STPO first as per the matnr and then use for all entries to get the data from MARA and MBEW.

Joining these three tables is not adviceable.

Rgrds,

Rahul.

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:25:04 GMT

hi,

check below coding..

tables: mara,stpo,mbew.

data: begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

extwg like mara-extwg,

verpr like mbew-verpr,

idnrk like stpo-idnrk,

end of itab.

select a_{matnr a}mtart a_{extwg b}verpr c~idnrk from mara

as a left outer join mbew as b on a_{matnr eq b}matnr

left outer join stpo as c on a_{matnr eq c}idnrk

into corresponding fields of table itab.

Regards,

Prabhu.

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:30:28 GMT

Hi,

I think your requirement is to retieve the BOM Component information,if it is your requirement then use this Function module.

CS_BOM_EXPL_MAT_V2

You will get all the components related to material then get costs from MBEW.

Thanks,

Suman.

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:36:07 GMT

Hi Supriya,

Try the following code :

tables :
 mara,
 stpo,
 mbew.
 
data :
  begin of t_mat occurs 0,
    matnr like mara-matnr,
    mtart like mara-mtart,
    extwg like mara-extwg,
    idnrk like stpo-idnrk,
    verpr like mbew-verpr,
  end of t_mat.  
  
 select m~matnr
        m~mtart
        m~extwg
        s~idnrk
        w~verpr
   from mara as m
   join stpo as s 
     on m~matnr = s~idnrk
   join mbew as w
     on m~matnr = w~matnr.

Regards,

Swapna.

Edited by: NagaSwapna Thota on Jul 8, 2008 7:36 AM

Edited by: NagaSwapna Thota on Jul 8, 2008 7:37 AM

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:37:45 GMT

Hiii!

Check out this code.


REPORT  z_sdn.

DATA:
  BEGIN OF fs_mat,
    matnr TYPE mara-matnr,
    mtart TYPE mara-mtart,
    extwg TYPE mara-extwg,
    verpr TYPE mbew-verpr,
    idnrk TYPE stpo-idnrk,
  END OF fs_mat.

DATA:
  t_mat LIKE
  TABLE OF
        fs_mat.


START-OF-SELECTION.
  PERFORM get_data.
  LOOP AT t_mat INTO fs_mat.
    WRITE: / fs_mat-matnr,
             fs_mat-mtart,
             fs_mat-extwg,
             fs_mat-verpr,
             fs_mat-idnrk.
  ENDLOOP.

FORM get_data .
  SELECT a~matnr
         a~mtart
         a~extwg
         b~verpr
         c~idnrk
    FROM mara
      AS a LEFT OUTER JOIN mbew AS
         b ON a~matnr EQ b~matnr
         LEFT OUTER JOIN stpo
      AS c ON a~matnr EQ c~idnrk
    INTO CORRESPONDING FIELDS OF TABLE t_mat.

ENDFORM.                    " get_data

Regards

Abhijeet Kulshreshtha

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 05:48:07 GMT

Hi Supriya,

tables: mara,stpo,mbew.

data: begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

extwg like mara-extwg,

idnrk like stpo-idnrk,

verpr like mbew-verpr,

end of itab.

select mara_{matnr mara}mtart mara_{extwg mbew}matnr stpo~idnrk from mara into corresponding fields of itab

JOIN mbew on mara_{matnr = mbew}matnr JOIN

stpo on mara_{matnr = stpo}idnrk.

See if it works

But joining three tables reduces performance analysis.

Reward points if useful to you.

Regards,

Sharmi.

Re: Joining 3 Tables

Former Member — Tue, 08 Jul 2008 06:34:19 GMT

Hi Supriya,

1) First query the stpo table based on matnr which is referred as idnrk and store in to an internal table i_stpo.

2) Take a temporary internal table i_tempstpo and store unique materials from i_stpo

3) Second query the mara table by using the i_tempstpo table with condition matnr eq i_stpo-idnrk and retrieve the required fields.

4) Third query the mbew table by using the i_tempstpo table with condition matnr eq i_stpo-idnrk and retrieve the required fields..

Please let me know if there are any issues.

Thanks,

Naveen.