inner join Vs For all entries

Hi Expert,

Could you please help me in this inner join , Inner join is performance wise is not good so i want to replace instated of inner join for all entries. But when i am checking records of internal table there is difference. in inner join i am getting 11 records but for all entries i am getting 5 records .

Please help me out where excatly i have done mistake.

• SELECT mara_{lvorm mara}matkl

• mara_{matnr mara}meins

• mara_{prdha mara}spart

• marc_{ausss marc}bearz

• marc_{beskz marc}bstmi

• marc~wzeit

• marc_{disgr marc}maabc * marc_{dismm marc}dispo

• marc_{dispr marc}dzeit

• marc_{plifz marc}disls

• marc_{bstma marc}bstrf

• marc~basmg

• marc_{eisbe marc}fevor

• marc_{fxhor marc}lvorm

• marc_{matnr marc}mmsta

• marc_{prctr marc}sobsl

• marc_{tranz marc}werks

• marc~xchar

• marc_{lgpro marc}fhori

• marc~rgekz

• mbew_{bklas mbew}bwkey

• mbew_{lbkum mbew}salk3

• mbew_{matnr mbew}peinh

• mbew_{stprs makt}maktx

• makt_{matnr cepc}abtei

• cepc~prctr

• marc_{schgt marc}minbe

• marc~ekgrp

• marc~eprio

• marc~kausf

• marc~shflg

• marc~shzet

• marc~fabkz

• marc~lgrad

• marc~shpro

• marc~eislo

• marc~rwpro

• marc~lgfsb

• marc_{zzfdwe marc}zzfdwi

• marc_{zzsspe marc}zzsspi

• INTO TABLE i_matl

• FROM ( mara

• INNER JOIN marc

• ON marc_{matnr = mara}matnr

*

• INNER JOIN mbew

• ON mbew_{matnr = marc}matnr

• AND mbew_{bwkey = marc}werks

*

• INNER JOIN makt

• ON makt_{matnr = mara}matnr

*

• INNER JOIN cepc

• ON cepc_{prctr = marc}prctr

• AND cepc_{datbi >= sy-datum ) "* WHERE mara}lvorm IN sp$00094

• AND mara~matkl IN sp$00084

• AND mara~meins IN sp$00091

• AND mara~prdha IN sp$00066

• AND mara~spart IN sp$00085

• AND marc~disgr IN sp$00089

• AND marc~dismm IN sp$00067

• AND marc~dispo IN sp$00061

• AND marc~dispr IN sp$00083

• AND marc~fevor IN sp$00090

• AND marc~lvorm IN sp$00092

• AND marc~mmsta IN sp$00086

• AND marc~beskz IN s_beskz

• AND marc~sobsl IN sp$00068

• AND marc~werks IN sp$00062

• AND marc~xchar IN sp$00093

• AND marc~maabc IN sp$00099

• AND marc~lgrad IN s_lgrad

• AND marc~lgpro IN s_lgpro

• AND marc~rwpro IN s_rwpro

• AND marc~lgfsb IN s_lgfsb

AND marc~schgt INs_schgt * AND marc~shflg IN s_shflg

• AND marc~shzet IN s_shzet

• AND marc~fabkz IN s_fabkz

• AND marc~shpro IN s_shpro

• AND marc~eislo IN s_eislo

• AND marc~zzfdwe IN s_zzfdwe

• AND marc~zzfdwi IN s_zzfdwi

• AND marc~zzsspe IN s_zzsspe

• AND marc~zzsspi IN s_zzsspi

• AND mbew~bklas IN sp$00063

• AND mbew~lbkum IN sp$00088

• AND makt~maktx IN sp$00059

• AND makt~spras = c_en

• AND makt~matnr IN sp$00060

• AND cepc~prctr IN sp$00087.

I am using for all entries

• fetching data from marc data base table

SELECT matnr werks lvorm xchar mmsta

maabc ekgrp dispr dismm

dispo plifz ausss disls

beskz sobsl minbe eisbe

bstmi bstma bstrf fhori

rgekz fevor bearz tranz

basmg dzeit wzeit lgrad

prctr fxhor lgpro disgr

kausf rwpro lgfsb schgt

eprio shflg shzet fabkz

shpro eislo zzfdwe zzfdwi

zzsspe zzsspi

FROM marc INTO TABLE i_marc

WHERE werks IN sp$00062

AND lvorm IN sp$00092

AND xchar IN sp$00093

AND mmsta IN sp$00086

AND maabc IN sp$00099

AND dispr IN sp$00083

AND dismm IN sp$00067

AND dispo IN sp$00061

AND beskz IN s_beskz

AND sobsl IN sp$00068

AND fevor IN sp$00090

AND lgrad IN s_lgrad

AND lgpro IN s_lgpro

AND disgr IN sp$00089

AND rwpro IN s_rwpro

AND lgfsb IN s_lgfsb

AND schgt IN s_schgt

AND shflg IN s_shflg

AND shzet IN s_shzet

AND fabkz IN s_fabkz

AND shpro IN s_shpro

AND eislo IN s_eislo

AND zzfdwe IN s_zzfdwe

AND zzfdwi IN s_zzfdwi

AND zzsspe IN s_zzsspe

AND zzsspi IN s_zzsspi.

IF sy-subrc EQ 0.

SORT i_marc BY matnr werks.

ENDIF.

• DELETE ADJACENT DUPLICATES FROM i_marc comparing matnr werks.

*fetching data from mara data base table

IF NOT i_marc[] IS INITIAL.

SELECT matnr lvorm matkl

meins spart prdha

FROM mara INTO TABLE i_mara

FOR ALL ENTRIES IN i_marc

WHERE matnr = i_marc-matnr

AND lvorm IN sp$00094

AND matkl IN sp$00084

AND meins IN sp$00091

AND prdha IN sp$00066

AND spart IN sp$00085.

ENDIF.

IF sy-subrc EQ 0.

SORT i_mara BY matnr.

ENDIF.

• DELETE ADJACENT DUPLICATES FROM i_mara comparing matnr.

*fetching data from mbew data base table

IF NOT i_mara[] IS INITIAL.

SELECT matnr bwkey bwtar lbkum

salk3 stprs peinh bklas

FROM mbew INTO TABLE i_mbew

FOR ALL ENTRIES IN i_marc

WHERE matnr = i_marc-matnr

AND bwkey = i_marc-werks

AND bklas IN sp$00063

AND lbkum IN sp$00088.

ENDIF.

IF sy-subrc EQ 0.

SORT i_mbew BY matnr bwkey bwtar.

ENDIF.

*DELETE ADJACENT DUPLICATES FROM i_mbew comparing matnr bwkey bwtar.

*fetching data from makt data base table

IF NOT i_mara[] IS INITIAL.

SELECT matnr spras maktx

FROM makt INTO TABLE i_makt

FOR ALL ENTRIES IN i_mara

WHERE matnr = i_mara-matnr

AND maktx IN sp$00059

AND makt~spras = c_en.

ENDIF.

IF sy-subrc EQ 0.

SORT i_makt BY matnr spras.

ENDIF.

• DELETE ADJACENT DUPLICATES FROM i_makt.

*fetching data from cpec data base table

IF NOT i_marc[] IS INITIAL.

SELECT prctr datbi kokrs abtei

FROM cepc INTO TABLE i_cepc

FOR ALL ENTRIES IN i_marc

WHERE prctr = i_marc-prctr

AND prctr IN sp$00087

AND datbi >= sy-datum.

ENDIF.

IF sy-subrc EQ 0.

SORT i_cepc BY prctr datbi kokrs.

ENDIF.

*DELETE ADJACENT DUPLICATES FROM i_cepc.

LOOP AT i_cepc INTO wa_cpec.

read table i_marc into wa_marc with key

prctr = wa_cpec-prctr binary search.

if sy-subrc = 0.

i_matl-abtei = wa_cpec-abtei.

i_matl-prctr1 = wa_cpec-prctr.

i_matl-ausss = wa_marc-ausss.

i_matl-bearz = wa_marc-bearz.

i_matl-beskz = wa_marc-beskz.

i_matl-bstmi = wa_marc-bstmi.

i_matl-wzeit = wa_marc-wzeit.

i_matl-disgr = wa_marc-disgr.

i_matl-maabc = wa_marc-maabc.

i_matl-dismm = wa_marc-dismm.

i_matl-dispo = wa_marc-dispo.

i_matl-dispr = wa_marc-dispr.

i_matl-dzeit = wa_marc-dzeit.

i_matl-plifz = wa_marc-plifz.

i_matl-disls = wa_marc-disls.

i_matl-bstma = wa_marc-bstma.

i_matl-bstrf = wa_marc-bstrf.

i_matl-basmg = wa_marc-basmg.

i_matl-eisbe = wa_marc-eisbe.

i_matl-fevor = wa_marc-fevor.

i_matl-fxhor = wa_marc-fxhor.

i_matl-lvorm1 = wa_marc-fevor.

i_matl-matnr1 = wa_marc-matnr.

i_matl-mmsta = wa_marc-mmsta.

i_matl-prctr = wa_marc-prctr.

i_matl-sobsl = wa_marc-sobsl.

i_matl-tranz = wa_marc-tranz.

i_matl-werks = wa_marc-werks.

i_matl-xchar = wa_marc-xchar.

i_matl-lgpro = wa_marc-lgpro.

i_matl-fhori = wa_marc-fhori.

i_matl-rgekz = wa_marc-rgekz.

i_matl-schgt = wa_marc-schgt.

i_matl-minbe = wa_marc-minbe.

i_matl-ekgrp = wa_marc-ekgrp.

i_matl-eprio = wa_marc-eprio.

i_matl-kausf = wa_marc-kausf.

i_matl-shflg = wa_marc-shflg.

i_matl-shzet = wa_marc-shzet.

i_matl-fabkz = wa_marc-fabkz.

i_matl-lgrad = wa_marc-lgrad.

i_matl-shpro = wa_marc-shpro.

i_matl-eislo = wa_marc-eislo.

i_matl-rwpro = wa_marc-rwpro.

i_matl-lgfsb = wa_marc-lgfsb.

i_matl-zzfdwe = wa_marc-zzfdwe.

i_matl-zzfdwi = wa_marc-zzfdwi.

i_matl-zzsspe = wa_marc-zzsspe.

i_matl-zzsspi = wa_marc-zzsspi.

endif.

READ TABLE i_mara INTO wa_mara WITH KEY

matnr = wa_marc-matnr binary search.

i_matl-lvorm = wa_mara-lvorm.

i_matl-matkl = wa_mara-matkl.

i_matl-matnr = wa_mara-matnr.

i_matl-meins = wa_mara-meins.

i_matl-prdha = wa_mara-prdha.

i_matl-spart = wa_mara-spart.

READ TABLE i_makt INTO wa_makt WITH KEY

matnr = wa_mara-matnr binary search.

i_matl-matnr3 = wa_makt-matnr.

i_matl-maktx = wa_makt-maktx.

READ TABLE i_mbew INTO wa_mbew WITH KEY

matnr = wa_marc-matnr binary search.

i_matl-bklas = wa_mbew-bklas.

i_matl-bwkey = wa_mbew-bwkey.

i_matl-lbkum = wa_mbew-lbkum.

i_matl-salk3 = wa_mbew-salk3.

i_matl-matnr2 = wa_mbew-matnr.

i_matl-peinh = wa_mbew-peinh.

i_matl-stprs = wa_mbew-stprs.

• READ TABLE i_cepc INTO wa_cpec WITH KEY

• prctr = wa_marc-prctr.

APPEND i_matl.

CLEAR: wa_cpec, wa_mbew, wa_mara, wa_marc.

ENDLOOP.

Thaks

Waiting your reply.