Skip to Content
0
Former Member
Feb 09, 2009 at 06:24 AM

How to re-write this big SELECT Query with INNER JOINs?

512 Views

Hi Experts

I have a performance killer SELECT query with an inner join of 3 tables u2013 VBAP, VBAK and VBEP together, which populates records to an internal table INT_COLL_ORD. Based on these records selected, in another SELECT query, records are fetched from VBUK table to the internal table INT_VBUK.

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP E~EDATU
INTO TABLE INT_TAB_RES
FROM VBAP AS A INNER JOIN VBAK AS D 
ON D~VBELN EQ A~VBELN AND D~MANDT EQ A~MANDT
INNER JOIN VBEP AS E 
ON E~VBELN EQ A~VBELN AND E~POSNR EQ A~POSNR AND E~MANDT EQ A~MANDT
WHERE  A~VBELN IN s_VBELN AND
       D~auart in s_auart AND
       D~vkorg in s_vkorg AND
       D~vbtyp eq 'C'     AND
       ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
       OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
       A~ABGRU EQ SPACE AND
       A~MTVFP IN R_MTVFP AND
       A~PRCTR IN R_PRCT AND
       E~ETENR EQ '1'.

SORT INT_COLL_ORD BY VBELN POSNR ETTYP.
DELETE ADJACENT DUPLICATES FROM INT_TAB_RES COMPARING VBELN POSNR.
CHECK NOT INT_TAB_RES [] IS INITIAL.

SELECT VBELN UVALL CMGST INTO TABLE INT_VBUK
FROM VBUK FOR ALL ENTRIES IN INT_TAB_RES
WHERE VBELN = INT_TAB_RES-VBELN AND UVALL NE 'A'.

Now, the requirement is:

I want to split this query. Like, first join VBAK and VBUK first. With this selection, go to the inner join of VBAP and VBEP (on key VBELN) to get the results. How can I re-write this Query?

Please help.

Thx n Rgds