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: 

How to use Inner Join for these 4 table's

Former Member
0 Kudos

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.

4 REPLIES 4

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

0 Kudos

Hi Seshu,

Can u write Inner Join for the 3 tables.?

Thanks,

Gopi.

0 Kudos

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