topic Re: runtime issue in Application Development and Automation Discussions

runtime issue

Former Member — Tue, 11 Sep 2007 12:03:26 GMT

hi all,

the below statement is giving me a runtime error.

In the shortdumb I got the messages as

->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.

Can some one please suggest me a best replacement for this.

Also when I checked in ST05 this was taking higher time than any other statement and was in red highlight

SELECT vkdfs_{vkorg vkdfs}vbtyp vkdfs_{vbeln tvko}bukrs

vkdfs_{fkdat VKDFS}SPART

into (t_vkdfs-vkorg, t_vkdfs-vbtyp, t_vkdfs-vbeln,

t_vkdfs-bukrs, t_vkdfs-fkdat, T_VKDFS-SPART)

FROM vkdfs join tvko on ( vkdfs_{vkorg = tvko}vkorg )

JOIN VBAK ON ( VKDFS_{SPART = VBAK}SPART )

WHERE fkdat IN s_wadat

AND vkdfs~vkorg IN s_vkorg AND

VKDFS~SPART IN S_SPART AND

vkdfs~vbtyp <> 'C'.

Thank you.

Re: runtime issue

Former Member — Tue, 11 Sep 2007 12:12:34 GMT

You can remove the VBAK JOIN as you are not selecting any field from table VBAK.

Regards,

Arya

Re: runtime issue

RaymondGiuseppi — Tue, 11 Sep 2007 12:20:07 GMT

(1) As TVKO is a small table (relatively) you could read the whole table into an internal sorted table once at the load of program.

Then LOOP at your internal table read from VKDFS/VBAP and move fields missing from VBAK and TVKO

(2) You are joining VBAK and VKDFS via SPART field only? So for each record of VBAK in a division, you join ALL records of VKDFS in the same division, you miss some join-criteria, like VBELN or so?

Regards

Re: runtime issue

Former Member — Tue, 11 Sep 2007 12:30:53 GMT

Here

SELECT vkdfs_{vkorg vkdfs}vbtyp vkdfs_{vbeln tvko}bukrs

vkdfs_{fkdat VKDFS}SPART

into (t_vkdfs-vkorg, t_vkdfs-vbtyp, t_vkdfs-vbeln,

t_vkdfs-bukrs, t_vkdfs-fkdat, T_VKDFS-SPART)

FROM vkdfs join tvko on ( vkdfs_{vkorg = tvko}vkorg )

JOIN VBAK ON ( VKDFS_{SPART = VBAK}SPART )

WHERE fkdat IN s_wadat

AND vkdfs~vkorg IN s_vkorg AND

VKDFS~SPART IN S_SPART AND

vkdfs~vbtyp <> 'C'.

I will suggest -

1. Dont do the join on VBAK. you can get the document type in VKDFS-VBTYP if u r seraching for Sales Orders.

2. You arenot selecting into tables. So i guess it ends with an ENDSEELCT. Dont select into variables and then process. Declare an internal table and do the SELECT in one shot.

SELECT fld1 fld2 .......fldn

into TABLE t_itab

from some_table

where -

Ur code between SELECT and ENDSELECT ( ABAP4 processing) is making the DB Select to take a very longer period of time.

Re: runtime issue

Former Member — Tue, 11 Sep 2007 14:05:34 GMT

Ther is a limited amount of memory available for creation of an SQL statement to be sent to the Database - this used to be in the range 6K to 8K bytes I believe.

This space must hold the entire SQL statement and all the data values for it (ie values in the WHERE block). So if you construct a WHERE that uses a lot of "field IN select-option" type comparisons, and then fill these select-options with lots of values, you soon get past the limit.

in your example, you have S_WADAT, S_VKORG, S_SPART - so if for example the S_WADAT selection had 500 dates in it this would use over 4K bytes - over half of what is available.

The only solution is to decrease the number of entries - perhaps by using ranges instead of single entries, or perhaps by calling the select multiple times with subsets of the selection range and append the returned results.

Upgrade to a later database or to 64 bit architecture may change the limit - I dont know - suggest you check SAP OSS notes.

Andrew