topic Re: Regarding Select Query in Application Development and Automation Discussions

Regarding Select Query

Former Member — Tue, 11 Dec 2007 13:16:07 GMT

Hi Abapers,

Please give me solution for the below query

where xvbrk is a internal table which contains:

data: begin of xvbrk occurs 0.

include structure vbrk.

data: vstel like likp-vstel, "Shipping Point

tknum like vtfa-tknum, "Shipment Number BMS DEVK925435

kunnr like likp-kunnr, "Ship-to number

sshid like vttk-exti2, "Barcode number

scarr(4) type c, "1st 4 digits of Pro Number

sccno like vttk-exti1, "Pro Number

bezei like tvrot-bezei, "Carrier Name

vtfavbeln like vtfa-vbeln, "Shipping Unit for Pallets

vhilm like vekp-vhilm, "Pallets

pltno(6) type n, "Number of Pallets

ntgew(10) type n, "Net Weight in KGM

sadrregio like sadr-regio, "State of shipping point

name1 like sadr-name1, "Name of shipping point

vbrpline type i, "Line item Numbers in VBRP

printerror(1) type c, "Print error

fkimg like vbrp-fkimg. "Total billed qty.

data: end of xvbrk.

select * from vbrk into table xvbrk where

vbeln in s_vbeln and

fkart in s_fkart and

fktyp in s_fktyp and

vbtyp in s_vbtyp and

erdat in s_erdat and

vkorg in s_vkorg and

fkdat in s_fkdat and

land1 in s_land1 and

kunrg in s_kunrg and

kunag in s_kunag and

xblnr in s_xblnr.

if sy-subrc = 0.

Remove if there are no qty to be billed! BMS 5/28/04

loop at xvbrk.

select sum( fkimg ) into xvbrk-fkimg from vbrp

where vbeln = xvbrk-vbeln.

if xvbrk-fkimg = 0.

delete xvbrk.

else.

modify xvbrk.

endif.

endloop.

I want to remove select query from the loop please help me out immediately.

Regards,

Rizwana

Re: Regarding Select Query

Former Member — Tue, 11 Dec 2007 13:22:16 GMT

instead of the code given by u use the code mentioned below.

use for all entries in select query that is the most efficient way for fetching the data with better performance.

if sy-subrc = 0.

Remove if there are no qty to be billed! BMS 5/28/04

*loop at xvbrk.

select sum( fkimg ) into xvbrk-fkimg from vbrp

for all entries in xvbrk

where vbeln = xvbrk-vbeln.

if xvbrk-fkimg = 0.

delete xvbrk.

else.

modify xvbrk.

endif.

*endloop.

Re: Regarding Select Query

Former Member — Tue, 11 Dec 2007 13:33:11 GMT

Hi,

Use this.

1)Declare a table xvbrp.

2)select vbeln sum( fkimg ) into table xvbrp from vbrp

where vbeln in s_vbeln

and fkimg NE 0.

Modify ur select statement as

3)select * from vbrk into table xvbrk for all entries in xvbrp

where

vbeln = xvbrp-vbeln and

fkart in s_fkart and

fktyp in s_fktyp and

vbtyp in s_vbtyp and

erdat in s_erdat and

vkorg in s_vkorg and

fkdat in s_fkdat and

land1 in s_land1 and

kunrg in s_kunrg and

kunag in s_kunag and

xblnr in s_xblnr.

Finally with in loop.

4) Read table xvbrp with key vbeln = xvbrk-vbeln.

xvbrk-fkimg = xvbrp-fkimg.

modify xvbrk.

This will increase ur report's performance tremoundously.

Regards,

Subbu

Re: Regarding Select Query

Former Member — Tue, 11 Dec 2007 13:49:55 GMT

Hi Ravi,

I am not suppose to use aggregate function sum also please give me some solution.

regards,

Rizwana

Re: Regarding Select Query

Former Member — Tue, 11 Dec 2007 13:56:18 GMT

Hi subbu,

I am not supposed to use aggregate function sum.

Regards,

Rizwana

Re: Regarding Select Query

Former Member — Tue, 11 Dec 2007 14:06:25 GMT

Hi,

your code is giving syntax error saying that :

The addition "FOR ALL ENTRIES" excludes all aggregate functions please help me out.

Regards,

Rizwana