Skip to Content
Author's profile photo Adi Sieker

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

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.

/wp-content/uploads/2012/03/select_option_shortdump_84324.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
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.

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

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Syambabu Allu
      Syambabu Allu

      Hi,

       

      Me also faced same issues,now i got the solution Adi.

       

      Thanks,

      Syam

      Author's profile photo Former Member
      Former Member

      Hi Adi,

      In one of our custom reports we used personnel no. on selection screen as select options, our variant are setup such that we process a lot of customers. Range is 10000000-19999999

      and 70000000-79999999.We use this range to select data from table and there is no dump.

       

      I assume the dump will not occur if we given a range on the selection screen but say for example if we give around 1000 personnel no. on selection screen it will give the runtime error.

      SIGN  OPTION LOW

        I          EQ      1000000000

        I          EQ      1000000001

      and so on .......

      Please let me know what is your opinion on this and do you agree?

      Author's profile photo Adi Sieker
      Adi Sieker
      Blog Post Author

      Hi Bhanu,

       

      your assumption is correct. The error only appears when you have lots of entries in the select option. It has nothing todo with how many results you retrieve.

       

      A select option that is a range like this 10000000-19999999 is translated into the SQL fragment "fieldname BETWEEN low AND high". so it really doesn't add much to the length of the SQL statement.

       

      Cheers

        Adi

      Author's profile photo Former Member
      Former Member

      Thanks

      Author's profile photo Jitendra Soni
      Jitendra Soni

      Nice blog.

      Author's profile photo Former Member
      Former Member

      Nice solution !

      Author's profile photo Edwin Vleeshouwers
      Edwin Vleeshouwers

      A good clear description of why this happens. Well put!

      Author's profile photo VIJAYKRISHNA GUDALA
      VIJAYKRISHNA GUDALA

      Hi Adi, I came across this situation very recently. Thanks for the clarification. Good Info!

      Author's profile photo Former Member
      Former Member

      Great Job

      Thanks for sharing

       

      Cheers,

      Akberhusain

      Author's profile photo Otto Gold
      Otto Gold

      Hello.

      I just hit the wall with this problem and wanted to share what happened to me. I created a local type that had a similar structure to the table I was querying. The difference was that for two date fields I went for string type instead of the date type used in the table. The plan was to use INTO CORRESPONDING FIELDS OF TABLE <local type variable>. I got the very same dump. It took me a moment to realize it was the different data type that caused it and I didn't find this option in the blog. May this helps the future generations as well

       

      cheers Otto

      Author's profile photo Adi Sieker
      Adi Sieker
      Blog Post Author

      Hi Otto,

       

      are you sure that you got a Runtime Error DBIF_RSQL_INVALID_RSQL and a Exception CX_SY_OPEN_SQL_DB?

      I'm guessing that you probably got some kind of conversion or type mismatch error and that doesn't have anything to do with what I explained here. There are lots of way to get a short dump in a SELECT statement. This post is only about the SELECT-OPTIONS problem,

       

      Regards

         Adi

      Author's profile photo Nathan Jones
      Nathan Jones

      You could try using function module

      RSDS_RANGE_TO_WHERE.

      This function module converts the range into a proper where clause.

       

      Author's profile photo Former Member
      Former Member

      Thanks for sharing!

      Author's profile photo Former Member
      Former Member

      Hi All,

       

      Report end with short dump RUNTIME ERROR DBIF_RSQL_INVALID_RSQL which raised the exception CX_SY_OPEN_SQL_DB.If select option having more than 2000 records.

      In case user passes more than 2000 records in the selection report is giving dump because select option having limit (0-1999) which means only 2000 records you can pass in selection.

      But I have requirement here user will enter more than 2000 records in selection.

      So I have taken care of this issue like below

      * >>>> Used WHILE ..... ENDWHILE. to avoiding dump >>>>>>>

      *------------------------------------------------------------------------------------------------*

      *If passing more than 1999 records in select option -> report end with short dump.

      *The Dump says: RUNTIME ERROR DBIF_RSQL_INVALID_RSQL which raised the exception CX_SY_OPEN_SQL_DB

      * To avoid this 1.  Converted select option into range table (S_MATNR)

      *               2.  Spitting & Passing 0 - 1900 records inside the while loop. (Splitting load of select query)

      *               3.  Deleting records from range table in line number 1900

      *               4. Fetch the records from the table.

      *------------------------------------------------------------------------------------------------*

       

      LR_MATNR[] = S_MATNR[]

      *    To avoiding dump ==> if user enter more than 1999 material number in selection screen

      WHILE lr_matnr[] IS NOT INITIAL.

       

      * Copy 1 to 1900 records from lr_range_mfrpn to lr_range_mfrpn_copy.

          APPEND LINES OF lr_matnr[] FROM lc_1 TO lc_1900 TO lt_matnr_copy[].

       

      * Delete 1 to 1900 records from lr_range_mfrpn.

          DELETE lr_matnr FROM lc_1 TO lc_1900.

       

      * Get external materials

          SELECT matnr_ext                  " External Long Material Number

            APPENDING TABLE et_matnr

            FROM materialid

            WHERE matnr_int IN lt_matnr_copy.

       

          CLEAR:lt_matnr_copy[].

       

      ENDWHILE.

      *------------------------------------------------------------------------------------------------*

      Now again i am facing similar kind of issue:

      Please find below code:

      Here I try to split and pass 1900 or 200 records every step inside the loop but program is giving error as mentioned below:

      Short text

          Error in module RSQL of the database interface

       

      Possible error causes:

      O The maximum size of an SQL statement was exceeded.

      O The statement contains too many input variables.

      O The input data requires more space than is available.

      O...

      Code1:

          WHILE lr_herst[] IS NOT INITIAL OR lr_txt50[] IS NOT INITIAL.

       

       

      * Copy 1 to 1900 records from lr_range_mfrpn to lr_range_mfrpn_copy.

            APPEND LINES OF lr_herst[] FROM lc_1 TO 200 TO lr_herst_copy[].

            APPEND LINES OF lr_txt50[] FROM lc_1 TO 200 TO lr_txt50_copy[].

       

       

      * Delete 1 to 1900 records from lr_range_mfrpn.

            DELETE lr_herst FROM lc_1 TO 200.

      * Delete 1 to 1900 records from lr_range_mfrpn.

            DELETE lr_txt50 FROM lc_1 TO 200.

       

       

            DELETE lr_herst_copy WHERE low = space.

            DELETE lr_txt50_copy WHERE low = space.

       

       

            OPEN CURSOR l_cursor FOR

                    SELECT anln1

                           anln2

                           anlkl

                           typbz

                           txt50

                           herst

                           anlue

                           sernr

                      FROM anla

                      FOR ALL ENTRIES IN it_bseg

                      WHERE anln1 = it_bseg-anln1

                        AND txt50 IN lr_txt50_copy[]

                        AND herst IN lr_herst_copy[]

                        AND sernr IN s_imatn[].

       

       

            DO.

              FETCH NEXT CURSOR l_cursor APPENDING TABLE lt_anla PACKAGE SIZE lc_package..

              IF sy-subrc NE 0.

                EXIT.

              ENDIF.

            ENDDO.

       

       

            CLEAR: lr_txt50_copy[],lr_herst_copy[].

       

       

            CLOSE CURSOR l_cursor.

          ENDWHILE.

       

      Code2:

       

          WHILE lr_herst[] IS NOT INITIAL OR lr_txt50[] IS NOT INITIAL.

       

       

      * Copy 1 to 200 records from lr_range_mfrpn to lr_range_mfrpn_copy.

            APPEND LINES OF lr_herst[] FROM lc_1 TO 200 TO lr_herst_copy[].

            APPEND LINES OF lr_txt50[] FROM lc_1 TO 200 TO lr_txt50_copy[].

       

       

      * Delete 1 to 1900 records from lr_range_mfrpn.

            DELETE lr_herst FROM lc_1 TO 200.

      * Delete 1 to 1900 records from lr_range_mfrpn.

            DELETE lr_txt50 FROM lc_1 TO 200.

       

                    SELECT anln1

                           anln2

                           anlkl

                           typbz

                           txt50

                           herst

                           anlue

                           sernr

                      FROM anla

                      FOR ALL ENTRIES IN it_bseg

                      WHERE anln1 = it_bseg-anln1

                        AND txt50 IN lr_txt50_copy[]

                        AND herst IN lr_herst_copy[]

                        AND sernr IN s_imatn[].

       

            CLEAR: lr_txt50_copy[],lr_herst_copy[].

       

       

       

          ENDWHILE.

       

      Kindly let me know how to handle this without using LDB?


      Thanks

      Akshath

      Author's profile photo Nathan Jones
      Nathan Jones

      You could always use the function module RSDS_RANGE_TO_WHERE if you are using ranges. This will create a where clause that should shrink it to bypass the limit in SAP.