topic Re: How to split this select query in Application Development and Automation Discussions

How to split this select query

Former Member — Tue, 29 Sep 2009 12:12:05 GMT

Hi all,

Can any one help me to split this single select query into 9 select query.

select KNA1_{STCEG VBRK}BUKRS VBRK_{VKORG VBRK}VBELN VBRK_{FKDAT VBRK}FKART VBRK_{VBTYP VBRK}VBUND VBRK_{KUNAG VBRK}KUNRG VBRK~NETWR

VBRK_{WAERK VBRK}FKSTO VBRK_{SFAKN VBRK}LAND1 T001W_{KUNNR VBRP}POSNR VBRP_{WERKS VBRP}FKIMG VBRP_{VRKME VBRP}PRSDT

VBRP_{NETWR VBRP}VGBEL VBRP_{VGPOS VBRP}MATNR VBRP_{PRCTR VBRP}CHARG VBRP_{AUBEL VBRP}AUPOS VBRP_{VBELN T001}WAERS T001~BUKRS

MBEW_{STPRS MBEW}PEINH MBEW_{MATNR MBEW}BWKEY LIKP_{LFART LIKP}WERKS LIKP_{VBELN MBEW_RECV}STPRS MBEW_RECV~PEINH

MBEW_RECV_{BWKEY MBEW_RECV}MATNR CKMLCR_{BDATJ CKMLCR}POPER CKMLCR_{STPRS CKMLCR}WAERS CKMLCR_{PEINH CKMLCR}CURTP

CKMLCR_{KALNR CKMLCR_RECV}STPRS CKMLCR_RECV_{WAERS CKMLCR_RECV}PEINH CKMLCR_RECV_{CURTP CKMLCR_RECV}BDATJ CKMLCR_RECV~POPER

CKMLCR_RECV~KALNR

from ( KNA1

left outer join VBRK

on VBRK_{KUNRG = KNA1}KUNNR

inner join T001W

on T001W_{KUNNR = VBRK}KUNAG

inner join VBRP

on VBRP_{VBELN = VBRK}VBELN

inner join T001

on T001_{BUKRS = VBRK}BUKRS

left outer join MBEW

on MBEW_{MATNR = VBRP}MATNR

and MBEW_{BWKEY = VBRP}WERKS

left outer join LIKP

on LIKP_{VBELN = VBRP}VGBEL

inner join MBEW as MBEW_RECV

on MBEW_RECV_{BWKEY = T001W}BWKEY

and MBEW_RECV_{MATNR = VBRP}MATNR

inner join CKMLCR

on CKMLCR_{KALNR = MBEW}KALN1

inner join CKMLCR as CKMLCR_RECV

on CKMLCR_RECV_{KALNR = MBEW_RECV}KALN1 )

where VBRP~WERKS in SP$00005

and VBRP~MATNR in SP$00008

and VBRP~CHARG in SP$00009

and VBRP~AUBEL in SP$00017

and CKMLCR~CURTP in SP$00015

and CKMLCR~BDATJ in SP$00013

and CKMLCR~POPER in SP$00014

and CKMLCR_RECV~CURTP in SP$00019

and CKMLCR_RECV~BDATJ in SP$00020

and CKMLCR_RECV~POPER in SP$00018 .

Re: How to split this select query

nikhil_chitre — Tue, 29 Sep 2009 12:14:45 GMT

hello,

In your select quesry you have used inner join....this will affect the performance of you code.

instead use select with addition FOR ALL ENTRIES.

for more details refer to the SAP help and build up your select queries.

Regards,

Nikhil

Re: How to split this select query

Former Member — Tue, 29 Sep 2009 12:17:04 GMT

split the selects at each join and make different select queries with using FOR ALL ENTRIES.

and select from header tables first and then select from lines item tables

Re: How to split this select query

Former Member — Tue, 29 Sep 2009 12:21:26 GMT

Just split your query as 9 different select queries on the 9 tables you have used in the join. Connect each select query with the ON condition in the join query as the where condition with FOR ALL ENTRIES.

This is the least you can expect. Hope you are not expecting someone to send a code with 9 different select queries. That would be against forum rules.

Regards,

Vikranth

Re: How to split this select query

Former Member — Tue, 29 Sep 2009 12:30:15 GMT

Hi, it is not good for your performance to split into 9 selects but the select you are now using is also not so good. Start with VBRP instead of KNA1.

You can do it in the following way. For example:

DATA tb_kna1 TYPE STANDARD TABLE OF kna1.

DATA tb_vbrk TYPE STANDARD TABLE OF vrbk.

DATA tb_vrbp TYPE STANDARD TABLE OF vrbp.

Start with VRBP for a better performance

SELECT * FROM vrbp

INTO CORRESPONDING FIELDS OF TABLE tb_vbrp

WHERE werks IN .... etc.

SELECT * FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE tb_vbrk

FOR ALL ENTRIES IN tb_vrbp

WHERE vbeln = tb_vrbp-vbeln.

SELECT * FROM kna1

INTO CORRESPONDING FIELDS OF TABLE tb_kna1

FOR ALL ENTRIES IN tb_vrbk

WHERE kunnr = tb_vrbk-kunnr.

Success.