runtime error in SQL statement

[Former Member]

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

[Former Member]

Welcome to SDN

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

regards

[Former Member]

hi,

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

cheers,

sasi

[Former Member]

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.

[Former Member]

sanjay thanks for info

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

what do you say

regards

[Former Member]

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

[Former Member]

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

[Former Member]

Sanjay,

thanks a lot for this info

regards

i did not know this initially

[Former Member]

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

[Former Member]

Please once can you post you whole code

regards

[manuel_bassani]

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

[Former Member]

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

Srinivas