Dump after select statement

[Former Member]

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?

[Former Member]

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.

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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

[ThomasZloch]

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

[Former Member]

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

[Former Member]

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?

[ThomasZloch]

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

[Former Member]

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 .

[Former Member]

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.

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.

[Former Member]

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.