topic Error during runtime in Application Development and Automation Discussions

Error during runtime

Former Member — Fri, 18 Aug 2006 17:14:47 GMT

Hi All,

We have a problem with the following code. The code works fine for less number of data, however when there is huge data it dumps giving the following error CX_SY_OPEN_SQL_DB. In the dump the following are given the possible causes of errors.

The maximum size of an SQL statement has been exceeded.

The statement contains too many input variables.

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

The code where the dump occurs:

SELECT vbeln vkorg pkunag kdgrp bzirk pkunrg

matnr posnr SUM( kzwi3 ) SUM( kzwi4 ) SUM( kzwi5 )

INTO TABLE t_s502

FROM s502

WHERE ssour EQ space

AND vrsio IN s_vrsio

AND spmon EQ '000000'

AND sptag EQ '00000000'

AND spwoc EQ '000000'

AND spbup IN s_spbup

AND vkorg IN s_vkorg

AND kdgrp IN r_kdgrp

AND bzirk IN r_bzirk

AND pkunag IN r_kunnr

AND vbeln IN s_vbeln

AND matnr IN s_matnr

GROUP BY vbeln vkorg pkunag kdgrp bzirk pkunrg matnr posnr.

At runtime the r_kunnr has close to 7000 entries, s_spbup is for 6months.

Any ideas how to encounter this problem..

Regards

Brain.

Re: Error during runtime

sridhar_k1 — Fri, 18 Aug 2006 17:18:46 GMT

Number of entries in r_kunnr is the reason for the dump. refer to oss 635318 for the size restrictions of sql statements.

Try to reduce the number of entries in r_kunnr or move kunnr into an itab and use for all entries in itab statement.

REgards

Sridhar

Message was edited by: Sridhar K

Re: Error during runtime

uwe_schieferstein — Fri, 18 Aug 2006 17:25:22 GMT

Hello Brain

You can split a (too) large SELECT statement in smaller ones using the option

SELECT ... PACKAGE SIZE 1000.

(e.g.).

If the "full" SELECT yields 7000 entries with the option above the selection will exceute 7 times the SELECT statement restricting the read data to 1000 entries.

This reduces the performance yet you omit the dump.

Regards

Uwe

Re: Error during runtime

Former Member — Fri, 18 Aug 2006 17:25:33 GMT

Hi,

I believe the IN will allow only for a certain limit..since sap converts open SQL to native SQL it cannot exceed the limit..I am not sure of the limit..

Instead of IN use For all entries for the customer range internal table...

Thanks,

Naren

Re: Error during runtime

Former Member — Fri, 18 Aug 2006 18:17:30 GMT

Thanks Uwe, i ll try to use the package size and i ll keep u posted.

Regarding the for all entries, i cant use it as i need duplicates as well.

Regards

Brain