topic Re: Dump after execution of select statement.. in Application Development and Automation Discussions

Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 10:43:58 GMT

Hello Techies ,

Am facing an issue while executing a select query where clause .

Select fieldvalue INTO TABLE me->t_values FROM zvlv_index

WHERE logid IN t_logid

AND fieldid = n_field group by fieldvalue.

in test system when i execute its running fine and i have observed tht records in table tht being used in WHERE clause are 6008 .

in prod system when i execute its giving DUMP and i have observed tht records in table tht being used in WHERE clause is 10244.

and i heard tht if there are more than 10k records in WHERE clause then it can give dump.

i got EXCEPTION CLASS : CX_SY_OPEN_SQL_DB

ERROR NAME : DBIF_RSQL_INVALID_RSQL.

Re: Dump after execution of select statement..

FredericGirod — Tue, 06 Aug 2013 11:08:35 GMT

Hi Praveen,

could you make an SQL trace, get the exact SELECT statement that is called by your program and give it ?

maybe you make a group by on a field not present in a select ...

regards

Fred

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:16:33 GMT

Range table when used in Select..IN format has a size limit, governed by server memory.

My system gives dumps if range table has 2000+ records.

You can change select query to Select..for all entries in.. format to avoid dump.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:22:07 GMT

Hi Praveen

as there are 10000+ records in t_logid, it is going to dump

use for all entries in t_logid in the select statement to avoid the dump

-Srini

Re: Dump after execution of select statement..

swen_hettstedt2 — Tue, 06 Aug 2013 11:30:32 GMT

Hy,

read this. http://scn.sap.com/thread/3388378

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:32:09 GMT

Hi Praveen,

Your understanding is correct. In the where clause if you use ranges using IN statement, SAP system provides a dump if the number of records in range object is more than 9999 entries.

You can see that the same thing happens when you try to provide more than 9999 entries in SE16.

The best option is to change the code to use for all entries instead of using IN.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:40:30 GMT

Hi Praveen,

In that case break the where condition values into two tables and write two select queries.

Thanks,

Govind

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:51:38 GMT

Hi manish ,

i tried like this ...

If not t_logid[] is initial.

sort t_logid.

select fieldvalue into table me->t_values from zvlv_index

for all entries in t_logid

where logid = t_logid-low

and fieldid = n_field.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 11:52:59 GMT

Hello techies,

am new to abap and i dont have much idea how to use for all entries .

can you post how to use it ?

Re: Dump after execution of select statement..

former_member206439 — Tue, 06 Aug 2013 13:32:23 GMT

Are you still getting the SHort dump after this select query?

What was the Short Dump Exception name?

The Select Query with For All Entries looks good.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 13:47:41 GMT

Hi Praveen,

A simple example you need to get material and its description for a report.

select matnr from mara into table it_mara.

if it_mara is not initial.

select maktx from makt into table it_makt

for all entries in it_mara

where matnr = it_mara-matnr.

endif.

it means for all the materials you fetched from mara table you will get the description from makt table.

Possiblities of error.

Check whether the structure declared for int table and the order in select are same.

if you can't find out from the above inputs then it might be the cause.

Check and revert for further queries.

Thanks,

Dinesh.

Re: Dump after execution of select statement..

ThomasZloch — Tue, 06 Aug 2013 13:53:50 GMT

The limit is not any exact number of records in selection criteria, but the size of the resulting SQL statement in KB:

Thomas

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 14:01:27 GMT

Hi Praveen,

For all entries is like this:

if lt_itab is not initial.

select fieldvalue

from dat_table

into lt_inttable

for all entries in lt_itab

where sel_field = lt_itab-sel_field.

if sy-subrc eq 0.

endif.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 14:35:34 GMT

Hello Dinesh ,

Can elaborate your line saying :

"check whether the structure declared for internal table and order in select are same" ?

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 14:48:07 GMT

Hello Govind ,

i did not got your answer , canyou come with an elaborate explanation taking exmple of my code which i had written previously.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 15:05:58 GMT

Hi Praveen,

See the int table decln

types : begin of ty_mara,

matnr type mara-matnr,

ersda type mara-ersda,

ernam type mara-ernam,

end of ty_mara.

data it_mara type table of ty_mara.

select ersda mara ernam from mara into it_mara.

here the sequence declared in structure and used in select are different..

also in select use fields in order as they are in the table.

these two will also lead to dump.

Thanks,

Dinesh.

Re: Dump after execution of select statement..

Former Member — Tue, 06 Aug 2013 15:32:26 GMT

Hi Praveen,

If you have an option to write FOR ALL ENTRIES then better to go for it.

If you dont have an option and need to fetch all the records using where condition table which contain more than 9999 records in your case it is 10244. Then break that values into two internal table one contains 9999 reords and second one 245 recods( Sum is 10244). And write two select queries and appending into single table.

Thanks,

Govind

Re: Dump after execution of select statement..

ThomasZloch — Wed, 07 Aug 2013 08:13:59 GMT

Hellooo, you cannot name an exact number like 9999 here, as I keep saying and saying. Depending on the length of each criteria, you might start seeing the dump with less or more entries.

It is the resulting size of the SQL statement that matters.

Here we only know that it works with 6008 and dumps with 10244 entries. The "limit" can be anywhere inbetween.

Thomas

Re: Dump after execution of select statement..

Former Member — Wed, 07 Aug 2013 10:53:14 GMT

Hello govind,

i got your answer , but when am trying that am not getting ,

we have to use package keyword or how we need to break internal table into two ?

Re: Dump after execution of select statement..

Former Member — Wed, 07 Aug 2013 13:46:05 GMT

Hi Praveen,

You can write following statement in order to break into two tables.

APPEND LINES OF <t_logid> FROM 1 TO 100 TO <t_logid1>.

APPEND LINES OF <t_logid> FROM 1 TO 100 TO <t_logid2>.

And then you can write two select queries. In second query write APPENDING TABLE <t_values>.

This is not good method. But if you have to complete your requirement then go for it. As developer we can fullfill requirement in any method.

Thanks,

Govind