topic Re: problem with a huge query in Application Development and Automation Discussions

problem with a huge query

Former Member — Mon, 14 Jul 2008 18:35:22 GMT

Hi guys, the following query ended with an error,

Possible errors:

o The maximum size of an SQL statement has been exceeded.

o The statement contains too many input variables.

o The space needed for the input data exceeds the available memory.

o ...

SELECT SUM( erlos ) SUM( vv010 ) SUM( absmg )

INTO (vtas, marg, v_pza)

FROM ce1pisa

where vrgar = 'F'

AND perio IN r_periodo

AND plikz = '0'

AND bzirk IN r_zonav

AND kndnr IN r_cliente

AND kmmakl IN r_linea

AND kmvkgr IN r_territorio

AND vkorg IN r_vkorg " ogr de ventas.

AND bukrs IN r_sociedad

AND vtweg IN r_canal.

r_vars are TYPE RANGE OF

i needo to know what to do, because i really need to use this query.

tks in advance.

Re: problem with a huge query

Former Member — Mon, 14 Jul 2008 18:40:00 GMT

Hello,

You need to be extra careful when using ranges in a select query.

There is a limitation on the maximum number of records that is allowed for range tables.

I have faced this situation myself when one of the ranges had more than 2500 records.

I split the records and the select query ran fine.

hope this helps.

cheers,

Sushil Joshi

Re: problem with a huge query

Former Member — Mon, 14 Jul 2008 18:41:52 GMT

Hi,

Check whether there is any master table for ce1pisa if that is itself master table then check the entries of other fields in other table linked to master table with key if so...

Select the master records into internal table.. and use FOR ALL ENTRIES in Second select query. This would be a good performance issue and your problem of huge query would be solved.

Regards

Narin Nandivada.

Re: problem with a huge query

Former Member — Mon, 14 Jul 2008 18:43:50 GMT

Hi,

Do not use aggregate functions at the database level. This will reduce performance hugely.

Select the fields into an internal table based on the where condition.

Loop at this internal table and use Collect stmt. This will be more efficient.

Regards,

Subramanian