topic Re: runtime error in SQL statement in Application Development and Automation Discussions

runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 07:37:08 GMT

hello,

I have a runtime during selection from database table due to large ranges in where condition. for example:

select * from cosp into corresponding

fields of table gt_cosp

where objnr in r_objnrs

and lednr = lv_lednr

and versn = p_vercost

and wrttp = lv_wrttp

and gjahr in r_gjahr

and kstar in r_kstar.

The suggested solution is to use internal table instead of a range and use "for all entries" or to divide the ranges to smaller units.

As you see I have few ranges and this solution could be not effective in this case.

Do you have a better idea?

Thanks in advance,

Far

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 07:47:27 GMT

Welcome to SDN

i don't think ranges can give any runtime error here

regards

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 07:54:24 GMT

hi,

can you post the error message and how manay entries you expect from the table

cheers,

sasi

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 07:55:46 GMT

Yes ranges have a limitation if number of entries are high. This depends on some database parameter.

If you are not sure of how many entries will be going in the ranges table at runtime, it is prferable to use "for all entries" , the compiler automatically fragments the select statements depending on database settings and you need not check this in ABAP code.

In our system select fails if ranges have more than 3000 entries.

Cheers.

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 08:01:50 GMT

sanjay thanks for info

i think we can set this number(3000) for no. of entries

what do you say

regards

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 08:04:49 GMT

Hi Fariba

You can tried to limit the number of records are loaded in table range.

So I don't know how you fill those range, but you do it in more steps than one.

For example:

LOOP AT ITAB.

R_RANGE(3) = 'IEQ'.

R_RANGE_LOW = ITAB-FIELD.

APPEND R_RANGE.

ENDLOOP.

SELECT * FROM <TABLE> INTO TABLE MY_TABLE

WHERE FIELD IN R_RANGE.

You could do:

DESCRIBE TABLE ITAB LINES MAX_LINE.

INDEX_FROM = 1.

INDEX_TO = 100.

DO.

LOOP AT ITAB FROM INDEX_FROM

TO INDEX_TO.

R_RANGE(3) = 'IEQ'.

R_RANGE_LOW = ITAB-FIELD.

APPEND R_RANGE.

ENDLOOP.

SELECT * FROM <TABLE> APPENDING INTO TABLE MY_TABLE

WHERE FIELD IN R_RANGE.

INDEX_FROM = INDEX_TO + 1.

IF INDEX_FROM > MAX_LINE.

EXIT.

ENDIF.

INDEX_TO = INDEX_TO + 100.

ENDDO.

In this way you select for all 100 hit.

How to solve your problem depend on how you have to fill the range.

Max

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 08:25:01 GMT

Surpreet ,

Not really the way you see . In database you have a limit on the length of SQL statement.

SAY

Select * from MARA where MATNR in S_MATNR.

The databse interpretor will split the above into

Select Field1 Field2 .... from mara

where matnr eq M1

or matnr eq M2

......

If you have large number of entries the select statement may reach the thresold value and will fail. Thresold value is a database parameter .

When runtime error occurs you can see this in SM21 system log and see which parameter caused runtime error .

( Other parameter may cause eroor as well ).

In ST05 you can see how select statements are passed to database.

"For all entries" takes care of above problem .

It splits the select statement depending on DB parameters dynamically.

Hope this is helpful.

Cheers

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 08:28:14 GMT

Sanjay,

thanks a lot for this info

regards

i did not know this initially

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 08:59:42 GMT

Thanks to all of you,

but unfortunately all these answers don't help in my case.

You see, "for all entries" could be helpful if each line in the range contain only one value...

in my case the "low" and "high" are different, and that's the reason I use range value.

(this range is reached by using a function witch split a group to values)

The above solution is too clumsy and complicated, and I was expected SAP has more intelligent solution.

Thanks in any case.

Fariba

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 09:02:20 GMT

Please once can you post you whole code

regards

Re: runtime error in SQL statement

manuel_bassani — Thu, 22 Sep 2005 09:12:23 GMT

Hi all,

I have had the same problem (with Oracle DB) and, looking for some SAP notes, I found the solution that max bianchi has suggested.

I think it's the only solution.

Regards, Manuel

Re: runtime error in SQL statement

Former Member — Thu, 22 Sep 2005 12:50:18 GMT

The easiest thing to do in all such cases is to get the records without that range and then delete them as follows.


select * from cosp 
         into corresponding fields of table gt_cosp 
        where lednr = lv_lednr 
          and versn = p_vercost 
          and wrttp = lv_wrttp 
          and gjahr in r_gjahr.
if not gt_cosp[] is initial.
  delete gt_cosp where not ( kstar in r_kstar and
                             objnr in r_objnrs ).
endif.

If you think your 'r_gjahr' will also contain lot of records, then you should move that also into the delete statement and take it out of the select statement.

Please don't forget to reward the helpful answers and close the post once resolved.

Regards,

Srinivas