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.
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:
|
| 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_rangewhat 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%2Fdoc%2Fr0001029.html
- DB2 Everyplace has a limit of 64kb. http://publib.boulder.ibm.com/infocenter/db2e/v9r1/index.jsp?topic=%2Fcom.ibm.db2e.doc%2Fdb2elimits.html
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
Hi,
Me also faced same issues,now i got the solution Adi.
Thanks,
Syam
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?
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
Thanks
Nice blog.
Nice solution !
A good clear description of why this happens. Well put!
Hi Adi, I came across this situation very recently. Thanks for the clarification. Good Info!
Great Job
Thanks for sharing
Cheers,
Akberhusain
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
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
You could try using function module
RSDS_RANGE_TO_WHERE.
This function module converts the range into a proper where clause.
Thanks for sharing!
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
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.