Currently Being Moderated

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.

select-option-shortdump.png

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
SIGNThe 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
LOWthe value to match.
HIGHonly used for the BT and NB options.

 

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

SIGNOPTIONLOW
HIGH
IEQ234567890
IEQ876543210
IBT10000000020000000

 

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.

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.
  • 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

Comments

Actions

Filter Blog

By author:
By date:
By tag: