Skip to Content
author's profile photo Former Member
Former Member

How to use Inner Join for these 4 table's

Hi Guys,

My Requirement is i have to get the data into the final Internal table whose structure is like this (BWKEY type MBEW-BWKEY,

MATNR type MBEW-MATNR,

MAKTX type MAKT-MAKTX,

BWTTY type MBEW-BWTTY,

BWTAR type MBEW-BWTAR,

MEINS type MARA-MEINS,

BKLAS type MBEW-BKLAS,

VPRSV type MBEW-VPRSV,

STPRS type MBEW-STPRS,

PEINH type MBEW-PEINH,

CRCY(3) type c,

LBKUM type MBEW-LBKUM,

SALK3 type MBEW-SALK3,

LAEPR type MBEW-LAEPR,

J_3ADEFSI type MARC-J_3ADEFSI,

J_4kDEFSC type MARC-J_4kDEFSC,

SCLAS type MARA-/AFS/SCLAS,

EKALR type MBEW-EKALR,

HKMAT type MBEW-HKMAT,

HRKFT type MBEW-HRKFT,

KOSGR type MBEW-KOSGR,

PRCTR type MARC-PRCTR,

MMSTA type MARC-MMSTA,

SOBSK type MARC-SOBSK,

PPRDL type MBEW-PPRDL,

LPLPR type MBEW-LPLPR,

PPRDZ type MBEW-PPRDZ,

ZPLPR type MBEW-ZPLPR)

from 4 table's MBEW , MARA MAKT and MARC.

Can anybody provide the logci for this based on the below requirement

"Go to table MBEW with material number, plant and valuation type; fetch the information laid out in output format. There are some fields which need to come from tables MARC, MARA and MAKT. The common criterion is material number across these tables.

MBEW-MATNR = MARC-MATNR

MBEW-MATNR = MARA-MATNR

MBEW-MATNR = MAKT-MATNR

"???

Thanks,

Gopi.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 11:07 PM

    Gopi,,

    I think better way would be get all the necessary fields from MBEW table into one internal table... then

    loop through that internal table get all the remaining fields from the remaining fields for all entries in the above internal table.....

    then move all the records into the final internal table with the structure u mentioned...

    that would improve the preformance I guess

    still if you want to go with the same one inner join

    consider the example below, if we want to retrieve data from these 4 tables MARA , MARC ,MARD , MAKT........Then our select query will be like this.

    SELECT AMATNR AMTART BWERKS CLABST D~MAKTX FROM

    MARA AS A INNER JOIN MARC AS B ON

    AMATNR = BMATNR INNER JOIN MARD AS C ON

    BMATNR = CMATNR INNER JOIN MAKT AS D ON

    CMATNR = DMATNR INTO CORRESPONDING FIELDS OF TABLE ITAB

    WHERE A~MATNR IN SO_MATNR.

    note : SO_MATNR will be variable in selection screen.

    by using these you will get fields from all 4 tables.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 11:21 PM

    Hello Gopi,

    Do not use joins for 4 tables,you may get bad performance.

    you can use upto 3 tables and then other table use for all entries.

    see the below logic( Pseudo code) ..and i did not have system otherwise i could give good pseudo code.

    types : begin of ty_mbew ,

    MATNR type MBEW-MATNR,

    BWTTY type MBEW-BWTTY,

    BWTAR type MBEW-BWTAR,

    MEINS type MARA-MEINS,

    BKLAS type MBEW-BKLAS,

    VPRSV type MBEW-VPRSV,

    STPRS type MBEW-STPRS,

    PEINH type MBEW-PEINH,

    CRCY(3) type c,

    LBKUM type MBEW-LBKUM,

    SALK3 type MBEW-SALK3,

    LAEPR type MBEW-LAEPR,

    J_3ADEFSI type MARC-J_3ADEFSI,

    J_4kDEFSC type MARC-J_4kDEFSC,

    SCLAS type MARA-/AFS/SCLAS,

    EKALR type MBEW-EKALR,

    HKMAT type MBEW-HKMAT,

    HRKFT type MBEW-HRKFT,

    KOSGR type MBEW-KOSGR,

    PRCTR type MARC-PRCTR,

    MMSTA type MARC-MMSTA,

    SOBSK type MARC-SOBSK,

    PPRDL type MBEW-PPRDL,

    LPLPR type MBEW-LPLPR,

    PPRDZ type MBEW-PPRDZ,

    ZPLPR type MBEW-ZPLPR,

    end of i_mbew.

    types : begin of ty_makt ,

    matnr type makt-maktx,

    MAKTX type MAKT-MAKTX,

    end of i_makt.

    types : begin of ty_final ,

    MATNR type MBEW-MATNR,

    MAKTX type MAKT-MAKTX,

    BWTTY type MBEW-BWTTY,

    BWTAR type MBEW-BWTAR,

    MEINS type MARA-MEINS,

    BKLAS type MBEW-BKLAS,

    VPRSV type MBEW-VPRSV,

    STPRS type MBEW-STPRS,

    PEINH type MBEW-PEINH,

    CRCY(3) type c,

    LBKUM type MBEW-LBKUM,

    SALK3 type MBEW-SALK3,

    LAEPR type MBEW-LAEPR,

    J_3ADEFSI type MARC-J_3ADEFSI,

    J_4kDEFSC type MARC-J_4kDEFSC,

    SCLAS type MARA-/AFS/SCLAS,

    EKALR type MBEW-EKALR,

    HKMAT type MBEW-HKMAT,

    HRKFT type MBEW-HRKFT,

    KOSGR type MBEW-KOSGR,

    PRCTR type MARC-PRCTR,

    MMSTA type MARC-MMSTA,

    SOBSK type MARC-SOBSK,

    PPRDL type MBEW-PPRDL,

    LPLPR type MBEW-LPLPR,

    PPRDZ type MBEW-PPRDZ,

    ZPLPR type MBEW-ZPLPR,

    end of i_final.

    • Internal tables

    data :i_mbew type standard table of ty_mbew,

    i_makt type standard table of ty_makt,

    i_final type standard table of ty_final.

    • Work Areas

    data : wa_mbew like line of i_mbew,

    wa_makt like line of i_makt,

    wa_final like line of i_final.

    start-of-selection.

    • get the data from MBEW,MARA,MARC Table

    select data from 3 tables

    if sy-subrc eq 0.

    use for all entries with respect makt table.

    endif.

    loop at i_mbew into wa_mbew.

    • read the data from i_makt.

    if sy-subrc eq 0.

    move all data i_mbew to i_final

    move maktx to i_final.

    append i_final.

    endif.

    endloop.

    Thanks

    Seshu

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hello Gopi,

      I did not have system now and just written in notepad..

      types : begin of ty_mbew ,

      MATNR type MBEW-MATNR,

      BWTTY type MBEW-BWTTY,

      BWTAR type MBEW-BWTAR,

      MEINS type MARA-MEINS,

      BKLAS type MBEW-BKLAS,

      VPRSV type MBEW-VPRSV,

      STPRS type MBEW-STPRS,

      PEINH type MBEW-PEINH,

      CRCY(3) type c, " What is this field ,i did not include in join

      LBKUM type MBEW-LBKUM,

      SALK3 type MBEW-SALK3,

      LAEPR type MBEW-LAEPR,

      J_3ADEFSI type MARC-J_3ADEFSI,

      J_4kDEFSC type MARC-J_4kDEFSC,

      SCLAS type MARA-/AFS/SCLAS,

      EKALR type MBEW-EKALR,

      HKMAT type MBEW-HKMAT,

      HRKFT type MBEW-HRKFT,

      KOSGR type MBEW-KOSGR,

      PRCTR type MARC-PRCTR,

      MMSTA type MARC-MMSTA,

      SOBSK type MARC-SOBSK,

      PPRDL type MBEW-PPRDL,

      LPLPR type MBEW-LPLPR,

      PPRDZ type MBEW-PPRDZ,

      ZPLPR type MBEW-ZPLPR,

      end of ty_mbew.

      types : begin of ty_makt ,

      matnr type makt-maktx,

      MAKTX type MAKT-MAKTX,

      end of ty_makt.

      types : begin of ty_final ,

      MATNR type MBEW-MATNR,

      MAKTX type MAKT-MAKTX,

      BWTTY type MBEW-BWTTY,

      BWTAR type MBEW-BWTAR,

      MEINS type MARA-MEINS,

      BKLAS type MBEW-BKLAS,

      VPRSV type MBEW-VPRSV,

      STPRS type MBEW-STPRS,

      PEINH type MBEW-PEINH,

      CRCY(3) type c,

      LBKUM type MBEW-LBKUM,

      SALK3 type MBEW-SALK3,

      LAEPR type MBEW-LAEPR,

      J_3ADEFSI type MARC-J_3ADEFSI,

      J_4kDEFSC type MARC-J_4kDEFSC,

      SCLAS type MARA-/AFS/SCLAS,

      EKALR type MBEW-EKALR,

      HKMAT type MBEW-HKMAT,

      HRKFT type MBEW-HRKFT,

      KOSGR type MBEW-KOSGR,

      PRCTR type MARC-PRCTR,

      MMSTA type MARC-MMSTA,

      SOBSK type MARC-SOBSK,

      PPRDL type MBEW-PPRDL,

      LPLPR type MBEW-LPLPR,

      PPRDZ type MBEW-PPRDZ,

      ZPLPR type MBEW-ZPLPR,

      end of ty_final.

      • Internal tables

      data :i_mbew type standard table of ty_mbew,

      i_makt type standard table of ty_makt,

      i_final type standard table of ty_final.

      • Work Areas

      data : wa_mbew like line of i_mbew,

      wa_makt like line of i_makt,

      wa_final like line of i_final.

      start-of-selection.

      • get the data from MBEW,MARA,MARC Table

      select aMATNR aBWTTY

      aBWTAR bMEINS

      aBKLAS aVPRSV

      aSTPRS aPEINH

      *CRCY(3) type c,

      aLBKUM aSALK3 a~LAEPR

      c~J_3ADEFSI type MARC-J_3ADEFSI,

      c~J_4kDEFSC type MARC-J_4kDEFSC,

      b~SCLAS type MARA-/AFS/SCLAS,

      aEKALR aHKMAT

      aHRKFT aKOSGR

      cPRCTR cMMSTA

      cSOBSK aPPRDL

      aLPLPR aPPRDZ

      a~ZPLPR into corresponding fields of table i_mbew

      from mbew as a inner join mara as b on bmatnr = amatnr

      inner join marc as c on cmatnr = amatnr.

      if sy-subrc eq 0.

      select matnr maktx from makt into table i_makt

      for all entries in i_mbew

      where matnr = i_mbew-matnr

      and spras = sy-langu.

      endif.

      loop at i_mbew into wa_mbew.

      • read the data from MAKT Table

      read table i_makt into wa_makt with key matnr = wa_mbew-matnr.

      if sy-subrc eq 0.

      move-corresponding wa_mbew to wa_final.

      move wa_makt-maktx to wa_final-maktx.

      append wa_final to i_final.

      endif.

      endloop.

      Thanks

      Seshu

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.