The answer to the question "So why do I get a shortdump when I have lots of entries in a select option?"

[former_member209685]

Apparently I answered a frequently asked question in this resurrected five year old thread. So I'll take the occasion to kick off my blogging experience on SCN.

The Problem

You executed a report and you are presented with this beautiful short dump. It's the runtime error dbif_rsql_invalid_rsql which raised the exception cx_sy_open_sql_db.

If you carefully read through the "Error analysis" you will notice that we seem to have hit some sort of size restriction imposed by the underlying database system. The cause for this shortdump is usually a ranges table with lots and lots of entries used in a SELECT statement. Another cause from a user perspective is executing a report where lots and lots of entries were added to a select option. But since a select option on a screen is the GUI representation of a ranges table the underlying cause is again the ranges table with lots and lots of entries.

The Cause

At some point in time, either directly in the report or in a function module/method that the report calls, the ranges tables that represents the select option was used to select some data from a database table. What happens now is that the SAP Database Interface generates an SQL statement, executes that statement on the database and returns the data to your internal table. Lets take a closer look at what exactly happens.

The entries of a ranges table have a structure with the following fields:

Fieldname	Description
SIGN	The sign field can have the values I or E to include or exclude this optionin the SQL result.
OPTION	The option field contains the operator to match the values from the LOW and HIGH fields against. The valid values are: EQ for = LOW NE for <> LOW GT for > LOW GE for >= LOW LT for < LOW LE for <= LOW CP for like LOW NP for not likeLOW BT for between LOW and HIGH NB for not between LOW and HIGH
LOW	the value to match.
HIGH	only used for the BT and NB options.

Lets assume the we have a ranges table IT_RANGE with this content:

SIGN	OPTION	LOW	HIGH
I	EQ	234567890
I	EQ	876543210
I	BT	100000000	20000000

When the following ABAP statement is executed:

SELECT * FROM mytable INTO TABLE lt_datatab WHERE fieldname IN it_range

what happens is that the SAP Database Interface will generate a SQL statement similar to this one:

SELECT * from mytable

     WHERE ( fieldname = 234567890 OR fieldname = 876543210 )

           AND fieldname BETWEEN 10000000 AND 20000000;

Now if we have a ranges table with 10000 entries that include equal different values and use that in a select we get the following statement:

SELECT * FROM mytable

     WHERE fieldname = <first value of ranges table>

               OR fieldname = <second value of ranges table> ...and on and on and on...

If we assume a fieldname that is 6 characters long and the field value being 10 characters long we can easily calculate the SQL statements length. The extra 8 characters are 'OR' the operator, quotes for a char feld and spaces.

(6+10+8)*10000 = 234 Kilobytes

The actual problem is that all SQL databases have a maximum statement length.

• Oracle 11g has apparently a 64K limit. See http://bytes.com/topic/oracle/answers/899061-what-maximum-query-length-oracle
  This page http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:41742083561301 suggests that Oracle itself has no limits, but that tools and libraries might impose a limit.
• SQL-Server has about the same limitations a oracle http://msdn.microsoft.com/en-us/library/ms143432.aspx.
• For MaxDB the size is tuneable by the database parameter CommandBufferSizehttp://maxdb.sap.com/doc/7_8/44/bd1ec6a5d51388e10000000a155369/content.htm.
• DB2 for Linux,UNIX and Windows apparently has a Limit of 2 097 152 bytes. http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%...
• DB2 Everyplace has a limit of 64kb. http://publib.boulder.ibm.com/infocenter/db2e/v9r1/index.jsp?topic=%2Fcom.ibm.db2e.doc%2Fdb2elimits....

The Solutions

• If you have the problem when using the transaction se16 and you have an ECC system, use se16n instead. It's nicer anyway. :smile:
• If you experience the problem in a report that you or a colleague has control over use the PACKAGE SIZE or FOR ALL ENTRIES IN addition. Also see OSS Note 13607.
• If the problem exists in a report you do not have control over the only option that I know of is to use less entries in the select option.

The Finish

I hope I have shed some light onto the situation. Feedback critics and comments are always more than welcome and thank you thomas.zloch for proof reading the article.

Cheers
   Adi