topic Runtime error using range table in select query in Application Development and Automation Discussions

Runtime error using range table in select query

Former Member — Tue, 31 Mar 2009 05:04:03 GMT

I have to select tcodes from table tstc, based on the entries in ust12,

the entries in ust12-von and ust12-bis, these contains wild charcters also, and i have to selct all the tcodes from von to bis.

so ia m preparing a range table for the entries in ust12, and querying table tstc, then i am getting a runtime error with following description.

'If the problem occurred because an excessively large table was used

in an IN itab construct, you can use FOR ALL ENTRIES instead.'

but how can i use FOR ALL ENTRIES here, because if von = A* and bis = AB*,then i ahve to read all the entries from AAAA till ABZZ (may be something like this),

is there any way to write this query, with out runtime error.

there are total 15000 entries in ust12, i am preparing range table for 3000 entries each and querying tstc.

Thanks in advance

Best Regards

Amarender Reddy B

Re: Runtime error using range table in select query

Former Member — Tue, 31 Mar 2009 07:43:43 GMT

yes, you can.

You can use ranges in the same way as FOR ALL ENTRIES do it. Split the internal table into

smaller blocks before you use the ranges, Try something like the following

 
DESCRIBE TABLE rnage_tab LINES no_lines.

IF ( NO_LINES < 500 )
  .... normal select

ELSE.
   steps = no_lines / 500 + 0.5.

count = 0.
DO steps times.
   count = count + 1.
   idx_low  = 500 * ( count - 1 ).
   idx_high = 500 * ( count - 1 ).
   APPEND LINES OF range_tab FROM idx_low TO idx_high TO range_block.
   ... your select with range_block ...
* don't forget :   
   REFRESH range_bllock.
ENDDO.
ENDIF.

Depending on your statement you should reduce the blocksize a bit.

Siegfried

Re: Runtime error using range table in select query

ThomasZloch — Tue, 31 Mar 2009 07:59:07 GMT

Due to the nature of the UST12 entries, there will be a lot of duplicate VON / BIS ranges, so you should get rid of these first. This might reduce the range table significantly.

Also, I think there will be a problem with your example "von = A, bis = AB", since you cannot simply put this in a range as "low = A, high = AB", as this does not work with option CP, it will not give the desired result. So like you suspected, you must translate this yourself first into a range that gives the desired result.

Take a look at how program RSUSR010 does it, or function module SUSR_GET_TCODES_WITH_AUTH_LIST.

Thomas

Re: Runtime error using range table in select query

Former Member — Tue, 31 Mar 2009 08:28:31 GMT

Ranges table have a limit when used in a query. It depends on your database. It is usually around 800 - 1600 records. So either give the range as

 
r_table-sign = 'I'.
r_table-option = 'BT'.
r_table-low = lower_limit.
r_table-high = higher limit.

well there is no exact figure as to how many records there can be in the ranges table.

Re: Runtime error using range table in select query

Former Member — Wed, 01 Apr 2009 08:54:32 GMT

HI,

Instead of using equal use Like statment in you query. Like = ''. But for performance would not be as good as with '='.

This is the sample code from SAP help document.

PARAMETERS srch_str TYPE c LENGTH 20.

DATA text_tab TYPE TABLE OF doktl.

CONCATENATE '%' srch_str '%' INTO srch_str.

SELECT *

FROM doktl

INTO TABLE text_tab

WHERE doktext LIKE srch_str.

Run your report in background.

Regards,

Prashant.

Edited by: Prashant Kumar Upadhyay on Apr 1, 2009 10:55 AM

Re: Runtime error using range table in select query

Former Member — Wed, 01 Apr 2009 09:00:37 GMT

Thank you Thomas, The issue is solved