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

Dump after select statement

Former Member — Fri, 17 Jan 2014 07:43:01 GMT

Hello Experts!

I am facing an issue while executing a select query where clause.

Original query is like:

SELECT * FROM table1 INTO TABLE et_result

WHERE field1 IN is_crit-field1[]

AND field2 IN is_crit-field2[]

AND field3 IN is_crit-field3[].

EXCEPTION CLASS : CX_SY_OPEN_SQL_DB, ERROR NAME : DBIF_RSQL_INVALID_RSQL.

- It gives dump when no. of records in is_crit_field1[] are about 3000.

I know it is because of query size and I need to use FOR ALL ENTRIES or PACKAGE SIZE to avoid this dump. I am interested to use 'for all entries' however notice that the parameter is "IS_CRIT" which is a structure & contains fields of table type. "For all entries" table itab and not structure with table type fields.

How can I use FOR ALL ENTRIES in this query? Is it possible?

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 07:48:03 GMT

What is the problem you are facing if you use for all entries.

SELECT * FROM table1 INTO TABLE et_result

for all entries in is_crit

WHERE field1 IN is_crit-field1

AND field2 IN is_crit-field2

AND field3 IN is_crit-field3.

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 07:57:35 GMT

I don't have itab (internal table) available for 'for all entries'.

I have a structure IS_CRIT which is a structure & has fields (table type) - means all fields have internal tables.

Re: Dump after select statement

ThomasZloch — Fri, 17 Jan 2014 08:04:34 GMT

First of call congrats for finding the root cause and possible workaround, most posters here don't even manage to do this, although it is being discussed so frequently.

Since is_crit-field3 is a selection range (i.e. internal table) with lots of single values (field3-option = "EQ"), you could try to append all the field3-low values to a helper internal table, sort it, delete duplicates, and use that for the FOR ALL ENTRIES access.

Thomas

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 08:19:26 GMT

hi Neetu

SELECT * FROM table1 INTO TABLE et_result1

for all entries in is_crit-field1

WHERE field1 IN is_crit-field1-field1.

SELECT * FROM table1 INTO TABLE et_result2

for all entries in is_crit-field2

WHERE field2 IN is_crit-field2-field2.

SELECT * FROM table1 INTO TABLE et_result3

for all entries in is_crit-field3

WHERE field3 IN is_crit-field3-field3.

sort et_result2 by filed2.

sort et_result3 by filed3.

loop at et_result1.

lv_index = sy-tabix.

read table et_result2 with key field2 = et_result1-field2 binary search.

if sy-subrc eq 0.

read table et_result3 with ky field3 = et_result1-field3 binary search.

if sy-subrc ne 0 .

delete et_result1 index lv_index.

endif.

else.

delete et_result1 index lv_index.

endif.

endloop.

hope can help you .

regards,

Archer

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 08:32:43 GMT

Hello Neetu Singla,

Upto My understanding, you should Check whether IS_CRIT-Field1[] or IS_CRIT-Field2[] or IS_CRIT-Field3[] these Input contains any record or not, If these Input doesn't contains values then it fetch all records of the table so it will go to Dump So Better Check to Input fields before passing to select statement.

If IS_CRIT-Field1[] is not initial or IS_CRIT-Field2[] is not initial or IS_CRIT-Field3[] is not initial .

SELECT * FROM table1 INTO TABLE et_result

WHERE field1 IN is_crit-field1[]

AND field2 IN is_crit-field2[]

AND field3 IN is_crit-field3[].

endif.

I think for these scenrio, No Need of for all entires, If i m wrong Please let know .

Re: Dump after select statement

ThomasZloch — Fri, 17 Jan 2014 08:34:08 GMT

Breaking up the AND-conditions into several queries like this means that potentially there is far too much data being read from the database, only to toss most of it away again during complex internal table handling. I would not recommend this from a performance point of view, there might even be memory issues.

Thomas

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 08:38:05 GMT

yes, you are right.

Maybe he can convert is_crit-field1 and is_crit-field2 and is_crit-field3 to a range,

then use the SQL.

Archer

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 10:06:16 GMT

Thanks.

My query's WHERE clause has multiple fields with IN parameter.

SELECT * FROM table1 INTO TABLE et_result

WHERE field1 IN is_crit-field1[]

AND field2 IN is_crit-field2[]

AND field3 IN is_crit-field3[].

IS_CRIT structure fields are like this:

FIELD1 tabletype FIELD1

FIELD2 tabletype FIELD2

FIELD3 tabletype FIELD3

I cannot make one internal table for field1, field2, field3, because these are different fields..

Any more pointers?

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 11:12:57 GMT

Hi,

I guess you can create one internal table by using loop inside loop. This might be expensive but you can compensate by using parallel processing in select.

This is how you can do it if its unavoidable.

Loop at is_crit-field1 into lwa_field1.

loop at is_crit-field2 into lwa_field2.

loop at is_crit-field3 into lwa_field3.

lwa_final-field1 = lwa_field1-field1.

lwa_final-field2 = lwa_field2-field2.

lwa_final-field3 = lwa_field3-field3.

INSERT lwa_final INTO TABLE is_final.

endloop.

ENDLOOP.

Reason we have to loop inside loop inside loop is to collect all possible combinations of all tables.

I know this can be a exponential process, but looping is still better than doing multiple selects.

Then,

Select * from table1 into et_result

for all entries in is_final

where feidl1 eq is_final-field1

and feild2 eq is_final-field2

and feild3 eq is_final-field3.

The select block can be put into a parallel process by dividing your driver table i.e. li_final.

Make sure is_final contains unique entries for each field1,field2,field3 combination.

Re: Dump after select statement

ThomasZloch — Fri, 17 Jan 2014 12:18:37 GMT

I understood that only one of your criteria has that many single values (I mixed up field1 and field3 in my reply). If that's not the case, then my suggestion doesn't work. I don't have a better one at this point.

Thomas

Re: Dump after select statement

Former Member — Fri, 17 Jan 2014 19:32:54 GMT

Hello Neetu,

This is really simple. Follow the below steps.

1. Create an internal table Eg: it_tab with 3 fields field1, field2 and field3.

And also work area Eg: wa_tab.

2. Now populate the internal table it_tab as below:

wa_tab-field1 = is_crit-field1

wa_tab-field2 = is_crit-field2

wa_tab-field3 = is_crit-field3

Append wa_tab to it_tab.

3. Now use for all entries in select query as below:

Select * from table1 into table et_result

for all entries in it_tab

where field1 EQ it_tab-field1

and field2 EQ it_tab-field2

and field3 EQ it_tab-field3.

Regards,

Imran.