Getting Runtime Error with Where condition table size

[naveen_inuganti2]

Hi Experts,

I have SQL query like this.

 loop at itab into wa.
   concatenate 'PR' wa-f1 into wa_range-low.
   wa_range-sign = 'I'.
   wa_range-option = 'EQ'.
   append wa_range to r_range.
   clear wa_range.
  endloop.
  
  if r_range[] is not initial.
  sort r_range by low.
  delete adjascent duplicates from r_range.
   select <fields> from <db table> into jtab
    where f1 in r_range.
  endif.

This statement causing Runtime Error. Because r_range filling with 12,000 records.

And the Runtime Error saying following solution:

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

So I have modified above SQL like this:

loop at itab into wa.
   concatenate 'PR' wa-f1 into wa_range-low.
   wa_range-sign = 'I'.
   wa_range-option = 'EQ'.
   append wa_range to r_range.
   clear wa_range.
  endloop.
  
  if r_range[] is not initial.
  sort r_range by low.
  delete adjascent duplicates from r_range.
   select <fields> from <db table> into jtab FOR ALL ENTRIES IN R_RANGE
    where f1 = r_range-f1.
  endif.

But,

I Can't test this scenario in my Development System. So not sure if its resolves the problem or not.

Because there are no duplicates entries in R_RANGE, where FOR ALL ENTRIES can make difference.

Please suggest me your Ideas.

Thanks,

Naveen Inuagnti.

[naveen_inuganti2]

Hi Experts,

I have SQL query like this.

 loop at itab into wa. 
           concatenate 'PR' wa-f1 into wa_range-low. 
           wa_range-sign = 'I'. 
          wa_range-option = 'EQ'. 
          append wa_range to r_range. 
          clear wa_range. 
          endloop. 
         
        if r_range[] is not initial. 
           sort r_range by low. 
           delete adjascent duplicates from r_range. 
            select fields from dbtable into jtab where f1 in r_range.
          endif.

This statement causing Runtime Error. Because r_range filling with 12,000 records. So I have modified above SQL like this:

 loop at itab into wa. 
           concatenate 'PR' wa-f1 into wa_range-low. 
           wa_range-sign = 'I'. 
          wa_range-option = 'EQ'. 
          append wa_range to r_range. 
          clear wa_range. 
          endloop. 
         
        if r_range[] is not initial. 
           sort r_range by low. 
           delete adjascent duplicates from r_range. 
            select fields from dbtable into jtab for allentries in r_range where f1 = r_range-low.
          endif.

But, I Can't test this scenario in my Development System. So not sure if its resolves the problem or not. Because there are no duplicates entries in R_RANGE, where FOR ALL ENTRIES can make difference.

Please suggest me your Ideas.

Thanks, Naveen Inuagnti.

[Former Member]

This error sounds like the internal table for "all table entries" is empty

[naveen_inuganti2]

Internal table is having 12,000 records.

And,

I am using If condition to check internal table..!

[Former Member]

Hi,

internal table contains too much lines, therefore when trying to translate it to native SQL, created SQL query is too complex and exceeds limitations of DB. You have to split this select into several selects. Try to reduce a number of entries in internal table to find some meaningful value for number of lines which to use for one select. I think it will be somewhere abou 2000 records in internal table.

You have to care for this in both cases - using select-options and also FOR ALL ENTRIES. Maybe limits will be different, but there is always some limit. It depends also on database used and other things.

Regards,

Adrian

[naveen_inuganti2]

Try to reduce a number of entries in internal table to find some meaningful value for number of lines which to use for one select. I think it will be somewhere abou 2000 records in internal table.

But unfortunately there is no other option in this..case.

FYI, this is Ztable to target BW system.. So this is natural amount of data for BW...

Thanks,

Naveen.I

[Former Member]

When I wrote "try to reduce...", I meant only for finding meaningful value as a border. Of courese, in your program You have to work with all your lines. Let's say, that 2000 is a meaningful number. What I suggest it to perform select 6 times for 12000 lines:

APPEND LINES OF r_range FROM 1 TO 2000 TO r_range_temp[].
select  from  into jtab where f1 in r_range_temp INTOTABLE lt_target_table.
clear r_range_temp[].

APPEND LINES OF r_range FROM 2001 TO 4000 TO r_range_temp[].
select  from  into jtab where f1 in r_range_temp APPENDING TABLE lt_target_table.
clear r_range_temp[].

APPEND LINES OF r_range FROM 4001 TO 6000 TO r_range_temp[].
select  from  into jtab where f1 in r_range_temp APPENDING TABLE lt_target_table.
clear r_range_temp[].

....

But of course, do it in some DO ... ENDDO block. That's my idea. I was solving the same issue as You, and this worked for me.

Adrian

[naveen_inuganti2]

Hi Adrian,

Thanks for your inputs.

Please check following lines from Runtime Error Problem Solving Section:

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.

So, I applied FOR ALL ENTRIES.. But not sure about , above solution is appliacable even there are no dupliacte lines in 'IN TABLE'.

Hi Rob,

Thanks for your inputs.. But May I know the reason why you don't prefer RANGES table..?

Thanks,

Naveen Inuganti.

[Former Member]

I think it is just sloppy. RANGE tables are meant to be used with CHECK or IN. If a later programmer has to maintain your program, he or she is going to wonder why this range table is there. There won't be any question with a normal internal table.

Rob

[naveen_inuganti2]

I Agree

Thanks,

Naveen Inuganti

[naveen_inuganti2]

Ok...Friends.

Thanks for your inputs.

Finally I have released the transports.. with For All Entries in New Table with Only One Field.

I'll Come back here with results...

Thanks,

Naveen Inuganti.,

[naveen_inuganti2]

Yes, This is working perfectly.

Thanks SDN..

[SuhaSaha]

Hello Naveen,

What do you mean by :

Because there are no duplicates entries in R_RANGE, where FOR ALL ENTRIES can make difference.

Can you please explain this in detail ?

BR,

Suhas

[naveen_inuganti2]

Because there are no duplicates entries in R_RANGE, where FOR ALL ENTRIES can make difference.

As per runtime error, FOR ALL ENTRIES can resolve this issue.

My doubt is, anyway we are using sort, delete duplicates statments in additon to where condition table here.

So in this 12,000 records, there are no duplicate records.

As of my knowledge.. FOR ALL ENTRIES can make difference if there are any duplicate records, because it can avoid the duplicate records.

But not sure about this... Please let me know..

I tried, checking this new code with TEST CODE (Appended Ranges table with do..enddo untill 15,000) and its working fine. But here we are having duplicate records..right... This is what my doubt after the change.

[Former Member]

Your proposed solution should work; however, I would use some sort of standard internal table rather than a range table in the FOR ALL ENTRIES.

Rob