topic CX_SY_OPEN_SQL_DB runtime error during SELECT in Application Development and Automation Discussions

CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 06:33:28 GMT

Hello Friends

Access standard table with below method giving dump

DBIF_RSQL_INVALID_RSQL

CX_SY_OPEN_SQL_DB

current logic is

IF s_cust-low IS NOT INITIAL.
   SELECT a~kunnr b~name1 a~vkorg a~vtweg a~spart a~loevm INTO TABLE i_cust
            FROM knvv AS a
            INNER JOIN kna1 AS b ON a~kunnr EQ b~kunnr
            WHERE a~vkorg IN s_vkorg AND
                  a~vtweg IN s_vtweg AND
                  a~spart IN s_spart AND
                  a~kunnr IN s_cust AND
                  b~ktokd EQ c_sh.
   IF sy-subrc NE 0.
     MESSAGE i000(00) WITH text-008.
     EXIT.
   ENDIF.
ELSE.
   SELECT a~kunnr b~name1 a~vkorg a~vtweg a~spart a~loevm INTO TABLE i_cust
             FROM knvv AS a
             INNER JOIN kna1 AS b ON a~kunnr EQ b~kunnr
             WHERE a~vkorg IN s_vkorg AND
                   a~vtweg IN s_vtweg AND
                   a~spart IN s_spart AND
                   b~ktokd EQ c_sh.
ENDIF.

Now how can I improve its performance here please guide me

thanks

Meeta

Moderator message: Please use proper subject line in future.

Subject was edited by: Suhas Saha

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

former_member201275 — Mon, 11 Aug 2014 06:45:47 GMT

Please can you post the complete dump because this is not enough information to go on.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 06:58:13 GMT

Thanks Glen for your reply ,trying to share the full dump in attached file ,,but during reply am not getting attach file option so pasting the dump here ,

Runtime Errors	DBIF_RSQL_INVALID_RSQL
Exception	CX_SY_OPEN_SQL_DB
Date and Time	04.08.2014 12:10:44

----------------------------------------------------------------------------------------------------

\|Short text	\|
\|	Error in module RSQL of the database interface.	\|

----------------------------------------------------------------------------------------------------

\|What happened?	\|
\|	Error in the ABAP Application Program	\|
\|	\|
\|	The current ABAP program "Z_SH" had to be terminated because it has	\|
\|	come across a statement that unfortunately cannot be executed.	\|

----------------------------------------------------------------------------------------------------

\|What can you do?	\|
\|	Note down which actions and inputs caused the error.	\|
\|	\|
\|	\|
\|	To process the problem further, contact you SAP system	\|
\|	administrator.	\|
\|	\|
\|	Using Transaction ST22 for ABAP Dump Analysis, you can look	\|
\|	at and manage termination messages, and you can also	\|
\|	keep them for a long time.	\|
\|	Note which actions and input led to the error.	\|
\|	\|
\|	For further help in handling the problem, contact your SAP administrator	\|
\|	.	\|
\|	\|
\|	You can use the ABAP dump analysis transaction ST22 to view and manage	\|
\|	termination messages, in particular for long term reference.	\|
\|	\|

----------------------------------------------------------------------------------------------------

\|Error analysis	\|
\|	An exception occurred that is explained in detail below.	\|
\|	The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not caught	\|
\|	and	\|
\|	therefore caused a runtime error.	\|
\|	The reason for the exception is:	\|
\|	The SQL statement generated from the SAP Open SQL statement violates a	\|
\|	restriction imposed by the underlying database system of the ABAP	\|
\|	system.	\|
\|	\|
\|	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 ...	\|
\|	\|
\|	You can generally find details in the system log (SM21) and in the	\|
\|	developer trace of the relevant work process (ST11).	\|
\|	In the case of an error, current restrictions are frequently displayed	\|
\|	in the developer trace.	\|

----------------------------------------------------------------------------------------------------

\|How to correct the error	\|
\|	The SAP Open SQL statement concerned must be divided into several	\|
\|	smaller units.	\|
\|	If the problem occurred due to the use of an excessively large table	\|
\|	in an IN itab construct, you can use FOR ALL ENTRIES instead.	\|
\|	When you use this addition, the statement is split into smaller units	\|
\|	according to the restrictions of the database system used.	\|
\|	\|
\|	\|
\|	If the error occures in a non-modified SAP program, you may be able to	\|
\|	find an interim solution in an SAP Note.	\|
\|	If you have access to SAP Notes, carry out a search with the following	\|
\|	keywords:	\|
\|	\|
\|	"DBIF_RSQL_INVALID_RSQL" "CX_SY_OPEN_SQL_DB"	\|
\|	"Z_SH_CUSH_HAUL" or "Z_SH_CUSH_HAUL"	\|
\|	"START-OF-SELECTION"	\|
\|	\|
\|	\|
\|	\|

-------------------------------------------------------

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

rosenberg_eitan — Mon, 11 Aug 2014 07:08:12 GMT

Hi,

I did not get any dump .

I had to comment out the marked lines to get some data but that is all .

IMHO a single select will be enough in this case since you are using SELECT-OPTIONS (no need to use )

Regards.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

suresh_mamilla — Mon, 11 Aug 2014 07:13:44 GMT

Hi meeta,

I did not get any dump actually.

First of all, as part of Types structure you have declared seven fields but, while extracting the data from DB, you have mentioned only six fields. You have included one custom field in the Types structure, instead of this declare one final structure and declare the custom field in this and make use of it.And also check the order of fields, the order should match with the order of DB.

All the Selection fields are coming from KNVV table right so, first fetch the data from knvv and for all entries in knvv fetch data from kna1 instead of INNERJOIN.

Apologies for any mistake.

Thanks&Regards,

Suresh M

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

VenkatRamesh_V — Mon, 11 Aug 2014 07:14:26 GMT

Hi meeta,

You missed the START-OF-SELECTION. Syntex Avoid KTOKD key field in where condition or better create Secondary Index

START-OF-SELECTION.

IF s_cust[] IS NOT INITIAL.
   SELECT a~kunnr b~name1 a~vkorg a~vtweg a~spart a~loevm INTO TABLE i_cust
            FROM knvv AS a
            INNER JOIN kna1 AS b ON a~kunnr EQ b~kunnr
            WHERE a~vkorg IN s_vkorg AND
                  a~vtweg IN s_vtweg AND
                  a~spart IN s_spart AND
                  a~kunnr IN s_cust AND
                  b~ktokd EQ c_sh.
   IF sy-subrc NE 0.
     MESSAGE i000(00) WITH text-008.
     EXIT.
   ENDIF.
ELSE.
   SELECT a~kunnr b~name1 a~vkorg a~vtweg a~spart a~loevm INTO TABLE i_cust
             FROM knvv AS a
             INNER JOIN kna1 AS b ON a~kunnr EQ b~kunnr
             WHERE a~vkorg IN s_vkorg AND
                   a~vtweg IN s_vtweg AND
                   a~spart IN s_spart AND
                   b~ktokd EQ c_sh.
ENDIF.

Regards,

Venkat.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 07:15:11 GMT

This message was moderated.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

former_member201275 — Mon, 11 Aug 2014 07:16:48 GMT

The reaons for your dump is there in the message "The input data requires more space than is available."

Your select statement is for too large a dataset. You are going to have to change the select to add more variables, or else in your selection screen enter more criteria, but essentially the problem is you don't have enough space.

It could be that basis could help you with the space issue as well, but i think it is your select.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 07:20:05 GMT

It would be better if you would use the standard view KNA1VV which is already available to fetch your data.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 07:27:10 GMT

Thanks to all of you for your quick turn around ,,

My Apologies for missed to inform that this dump is coming in production due to huge amount of data while fetching data from these standard tables

so for the same , I have to change the code in such a way that it can handle the large volume of data ...

Regards

Meeta

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 07:33:28 GMT

Hi meeta,

While trying to use into corresponding always the ordering of fields is important.Just add a~Mandt in your select query.Add at the beginning of select query.

Or best solution create a structure of mentioned fields and push the data to internal table of same type as of a structure.

Both way will work.

Hope it will be helpful.

Regards,

Kannan

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

former_member201275 — Mon, 11 Aug 2014 07:40:09 GMT

It is what i mentioned the dataset is too large. Have you correct table index for these selects? Do you know which of the 2 selects is causing this dump?

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 07:46:19 GMT

Hi,

how many entries where in s_cust? Maybe this Itab was too big for the selectstatement?

regards

Stefan Seeburger

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Mon, 11 Aug 2014 08:06:12 GMT

Hi Glen .. in the dump its showing at row 66 for dump for select query but how to check from exactly which table its happening ?

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

former_member201275 — Mon, 11 Aug 2014 11:19:35 GMT

I cannot tell from the screen dump above which table, but you should run performance monitoring for this to check all database selects. There are many other replies on here which are good and which you should also try out. You will have to play around a bit and test in order to come up with the best solution for your purpose.

Re: CX_SY_OPEN_SQL_DB runtime error during SELECT

Former Member — Tue, 12 Aug 2014 08:38:22 GMT

Hello All

The problem resolve by reducing the size of data in the selection range ,

Thanks to all for your on time valuable response .