CX_SY_OPEN_SQL_DB runtime error during SELECT

[Former Member]

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

[former_member201275]

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

[Former Member]

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"	|
|	|
|	|
|	|

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

[former_member201275]

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.

[rosenberg_eitan]

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.

[suresh_mamilla]

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

[VenkatRamesh_V]

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.

[Former Member]

This message was moderated.

[Former Member]

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

[Former Member]

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

[former_member201275]

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?

[Former Member]

Hi,

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

regards

Stefan Seeburger

[Former Member]

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 ?

[former_member201275]

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.

[Former Member]

Hello All

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

Thanks to all for your on time valuable response .

[Former Member]

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