topic Re: Select-options maximum limit??? in Application Development and Automation Discussions

Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:18:45 GMT

Hi all,

I guess there is a maximum limit that a select-options can hold. If so, what is the maximum number of single values that it can hold upto?

Thanks,

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:23:50 GMT

Hi,

I dont think, There will be maximum limit for SELECT-OPTIONS.

As its a kind of internal table, so everytime it will increase the memory size like internal tables.

Regs

Manas

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:27:30 GMT

Hi,

As SELECT-OPTIONS are internal tables. The rules applied to number of lines to an internal table will also applicable to SELECT-OPTIONS too.

<b>The characterctis of an internal table in number of lines:</b>

The only restriction on the number of lines an internal table may contain are the limits of your system installation. The maximum memory that can be occupied by an internal table (including its internal administration) is 2 gigabytes. A more realistic figure is up to 500 megabytes. An additional restriction for hashed tables is that they may not contain more than 2 million entries.

Thanks,

Vinay

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:27:39 GMT

i guess it is around 1600 - 1700

chk this program , if u increase the do loop to 1800 it will give dump



REPORT ychatest LINE-SIZE 350.

TABLES : mara.
SELECT-OPTIONS : s_matnr FOR mara-matnr.

DATA : BEGIN OF itab OCCURS 0.
        INCLUDE STRUCTURE mara.
DATA:    END OF itab.

DO 1600 TIMES.
  s_matnr-sign = 'I'.
  s_matnr-option = 'BT'.
  s_matnr-low = ''.
  s_matnr-high = ''.
  APPEND s_matnr.
  CLEAR s_matnr.
ENDDO.

SELECT  * FROM mara INTO TABLE itab WHERE matnr IN s_matnr.

WRITE : 'hi'.

Re: Select-options maximum limit???

RichHeilman — Thu, 28 Dec 2006 14:28:04 GMT

Yes, there is a limit to the number of single values which the program can handle. It may depend on the system, but in my system, I believe it is somewhere between 800 - 1100

Regards,

Rich Heilman

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:37:20 GMT

limit is somewhere between 1000 - 1100

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:50:58 GMT

Thanks a lot. I tested the sample code. It doesn't take anything more than 1624.

I dont know whether it varies from system to system but in my system it takes maximum of 1624.

Thanks everyone.

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 14:53:12 GMT

Hi,

thanks for sharing the information

Re: Select-options maximum limit???

Former Member — Thu, 28 Dec 2006 15:15:52 GMT

Hello,

It's good to know that the maximum differs from table to table or should I say per data element. Thing is that all entries in the select-options are expanded in the SQL statement that is generated. The SQL statement passed to the database has its size limitations.

Please read SAP note 635318 :

Constructs with SELECT-OPTIONS (or RANGE tables), for example, "WHERE id IN itab", look completely different in the database.These constructs are converted to an AND/OR tree:all 'E' rows are linked with AND and all 'I' rows with OR.These two parts are then linked with AND.Depending on the database system, you can also map the I rows to IN (val1, val2, and so on) if they all have the operator 'EQ'. For a condition created in this way, points 1 and 2 apply.

The length of the statement in the database must not exceed 28672 characters (with Unicode, 14336 characters).

kind regards,

jeroen

Re: Select-options maximum limit???

Former Member — Thu, 13 Jun 2013 13:38:32 GMT

Hi,

But I have used more than 5000 Single Values in Table Selection Screen.. Even I don't think there would be any maximum limit. Can you make me clear in this ?

Arun

Re: Select-options maximum limit???

Former Member — Thu, 13 Jun 2013 13:44:18 GMT

Hi,

TABLES : mara.
SELECT-OPTIONS : s_matnr FOR mara-matnr.

DATA : BEGIN OF itab OCCURS 0.
         INCLUDE STRUCTURE mara.
DATA:    END OF itab.

DO 4800 TIMES.
   s_matnr-sign = 'I'.
   s_matnr-option = 'BT'.
   s_matnr-low = ''.
   s_matnr-high = ''.
   APPEND s_matnr.
   CLEAR s_matnr.
ENDDO.

SELECT * FROM mara INTO TABLE itab WHERE matnr IN s_matnr.

WRITE : 'hi'.

For my case it working fine. what will be the correct.

Re: Select-options maximum limit???

ThomasZloch — Thu, 13 Jun 2013 14:54:05 GMT

Re: Select-options maximum limit???

dave_price — Tue, 24 Mar 2015 18:43:10 GMT

Looking at the age of the thread, apparently SAP has evolved over this time to allow more entries.

I have run into the size limit on SQL statements in DBIF_RSQL_INVALID_RSQL when the selection table and the query were both large.

Re: Select-options maximum limit???

Former Member — Tue, 24 Mar 2015 18:52:57 GMT

The restriction is actually a database restriction, not SAP. See the note mentioned above.

Rob

Re: Select-options maximum limit???

dave_price — Tue, 24 Mar 2015 19:06:27 GMT

Databases do not limit SQL statements to 28672 characters, at least not since around version 9 of Oracle (and even in 8 it apparently wasn't enforced Ask Tom &quot;maximum length of sql statement&quot; ).

I'm not sure what the limit is in SAP6.0 or whether it is an SAP parameter, but I managed to blow it up by sending multiple selection-tables, one of which would break it with a few hundred rows, into a query against around fourteen different tables. As it happened it was a non-issue for us because they really wanted everything in that situation, so we just gave them that as an option.

Re: Select-options maximum limit???

Former Member — Tue, 24 Mar 2015 19:22:36 GMT

From note 635318 :

Caution: Individual database systems limit this area to 32,000 bytes. A machine-independent program may therefore not use more than 32,000 bytes for the comparison values in the WHERE, HAVING or ON condition of a command.

But this note is 9 years old. I haven't seen this problem in a while (DB6).

Rob

And I ran a quick test to see what might happen. I was able to run a SELECT with a range containing 15,000 entries, but it dumped with 16,000 entries.

So I guess that SAP may have a hard limit on the size of a SELECT-OPTION, but that it is based on underlying database limitations.

Message was edited by: Rob Burbank

Re: Select-options maximum limit???

stephenl1 — Thu, 16 Jul 2020 21:35:42 GMT

The limit depends entirely on how you are using the select-options table. Because it is a table you can load any number of rows into it, but if you subsequently apply it for use against an SQL select statement, or as a table parameter passed to another program, which then applies it against an SQL select statement, then you are limited by the length of the SQL statement that will be generated.

Each Select-Options row will translate into code expanding the length of the SQL select statement, and so the more rows you have, the longer the resulting select statement will become. Also, the longer each data item in the select-options is, the longer the statement will become.

It is because of the fact that the data length of each select-option entry impacts the length, that people have given different limits in above posts, as the length directly impacts how many entries can be processed into the selection statement before it blows its limit.

You cannot see the resulting select statement, as the translation of the select-options into the IN clause is done behind the scenes.