topic Re: Need help to optimize query in Application Development and Automation Discussions

Need help to optimize query

Former Member — Tue, 18 Mar 2008 12:53:22 GMT

I need help to optimize the below query.... I takes to long to execute.

SELECT vbak~waerk

vbap_{vbeln vbap}posnr

vbap_{netwr vbap}brgew vbap~erdat

INTO CORRESPONDING FIELDS OF TABLE tmp_orders

FROM vbak INNER JOIN vbap ON vbak_{vbeln = vbap}vbeln

WHERE vbap~matnr EQ t_mchb-matnr

AND vbap~netwr GT 0

AND vbap~brgew GT 1

AND vbak~auart IN ('ZCAO', 'ZDDO').

Please help...

Edited by: Alvaro Tejada Galindo on Mar 18, 2008 6:02 PM

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 12:59:42 GMT

try with out "corresponding fields"

Thanks

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 12:59:49 GMT

Hi,

Check this code.

SELECT vbak~waerk

vbap_{vbeln vbap}posnr

vbap_{netwr vbap}brgew vbap~erdat

INTO ( tmp_order-waerk tmp_order-vbeln tmp_order-posnr tmp_order-netwr tmp_order-brgew tmp_order-erdat )

FROM vbak INNER JOIN vbap ON vbak_{vbeln = vbap}vbeln

WHERE vbap~matnr EQ t_mchb-matnr

AND vbap~netwr GT 0

AND vbap~brgew GT 1

AND vbak~auart IN ('ZCAO', 'ZDDO').

<REMOVED BY MODERATOR>

Regards.

Edited by: Alvaro Tejada Galindo on Mar 18, 2008 6:03 PM

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 13:05:24 GMT

Hi,

IF tmp_orders IS NOT INITIAL .

SELECT waerk

vbeln posnr

netwr brgew erdat from vbak

INTO CORRESPONDING FIELDS OF TABLE tmp_orders

FOR ALL ENTRIES IN vbeln

WHERE vbeln = vbap-vbeln

vbap-matnr EQ t_mchb-matnr

AND vbap-netwr GT 0

AND vbap-brgew GT 1

AND auart IN ('ZCAO', 'ZDDO').

Regards

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 13:05:37 GMT

Thanks Akshay....

I need to split the query into 2 and use for all entries.

I'm new to ABAP. so need help to do this.

Can u help me?

Thanks in Advance

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 13:15:38 GMT

Hi,

Check this code.

Here itab1 should have the fields vbeln waerk and itab2 should have the fields vbeln posnr netwr brgew erdat.

select vbeln waerk

from vbak

into itab1

where auart IN ( 'ZCAO', 'ZDDO').

if not itab1[] is initial.

select vbeln posnr netwr brgew erdat

from vbap

into itab2

for all entries in itab1

where vbeln = itab1-vbeln.

endif.

<REMOVED BY MODERATOR>

Regards.

Edited by: Alvaro Tejada Galindo on Mar 18, 2008 6:04 PM

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 13:15:57 GMT

Hi Karen,

Try this code


types: begin of tmp_order,
	waerk type  WAERK,
	vbeln type vbeln_va,
	posnr type posnr_va,
	netwr type netwr_ap,	
	brgew type BRGEW_AP,
	erdat type erdat,
	end of tmp_order.
data : i_tmp_order type standard table of tmp_order.
select	a~waerk
	b~vbeln
	b~posnr
	b~netwr
	b~brgew	
	b~erdat
from VBAK as a inner join vbap as b 
on a~vbeln = b~vbeln
into table i_tmp_order
where b~matnr = EQ t_mchb-matnr
AND b~netwr GT 0 
AND b~brgew GT 1 
AND a~auart IN ('ZCAO', 'ZDDO').

Regards

Sandipan

Re: Need help to optimize query

Former Member — Tue, 18 Mar 2008 14:38:11 GMT

Hi,

try this logic and see:

data: begin of itab_for_vbak occurs 0,

waerk type WAERK,

vbeln type vbeln,

end of itab_for_vbak,

wa_for_vbak like itab_for_vbak.

data: begin of itab_for_vbap occurs 0,

vbeln like vbap-VBELN,

posnr like vbap-POSNR,

netwr like vbap-NETWR,

brgew like vbap-brgew,

erdat like ERDAT

end of itab_for_vbap,

wa_for_vbap like itab_for_vbap.

SELECT

waerk

vbeln

from

vbak

into table itab_for_vbak

where

vbak~auart IN ('ZCAO', 'ZDDO').

select

vbeln

posnr

netwr

brgew

erdat

into table itab_for_vbap

where

vbap~matnr EQ t_mchb-matnr

AND vbap~netwr GT 0

AND vbap~brgew GT 1.

loop at itab_for_vbak into wa_for_vbak.

read table itab_for_vbap with key vbeln = wa_for_vbak-vbeln.

if sy-subrc eq 0.

tmp_orders-waerk = wa_for_vbak-waerk.

tmp_orders-vbeln = wa_for_vbap-vbeln.

tmp_orders-posnr = wa_for_vbap-posnr.

tmp_orders-brgew = wa_for_vbap-brgew.

tmp_orders-erdat = wa_for_vbap-erdat.

append tmp_orders.

endif.

endloop.

Please let me know how it has improved your performance or still if you have issues on this.

Thanks,

Vishnu.