topic Re: Delete internal table entries using range table w.multiple ranges in Application Development and Automation Discussions

Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 22:43:47 GMT

Hi,

I am trying to delete the entries in an internal table using a range table which has multiple (a lot) ranges but the delete statement will not take it.

delete int_tab where f1 not in range_tab

int_tab is the main internal table. Range_tab is a range table which only has sign, option, low and high as the fields but multiple rows.

It gives me a syntax error of <%_L003> is not an internal table - the "occurs n" specificiation is missing.

Does anybody have any suggestion on how I can delete the entries in the internal table where the value of f1 is not within the range of the range table?

Thank you,

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 22:50:14 GMT

Hi,

Just check if the occurs addition is there in internal table defination or not. if it is not there then it will behave as work area hence the error.

if you are declaring internal table with refrence to database table or structure then it should be like

data int_tab like vbak occurs 0 with header line.

if you are declaring the fields and declaring internal table then

Data : begin of int_tab occurs 0,

fi like ....,

f2 like ....,

end of int_tab.

Also change your delete statement as below

delete int_tab where not f1 in range_tab.

Hope this helps.

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 22:55:26 GMT

Hi,

Does your range_tab is defined like this:

DATA:

BEGIN OF sel_record,

sign(1),

option(2),

low LIKE [dictionary-type] ,

high LIKE [dictionary-type],

END OF sel_record.

DATA:

range_tab TYPE STANDARD TABLE OF sel_record.

The error message you're getting is that probably your range_tab is not defined as an internal table as it should be so the not in works as it should.

Hope it helps.

Regards,

Gilberto Li

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 23:00:25 GMT

Thanks for the response. Yes, both the main internal table and the range table are defined with "occurs n".

Actually, the reason why I need to delete the entries in the internal table is because it gives me a runtime error when I do the select usign the range table directly. The range table has more than 2K ranges.

I saw some discussion in this forum mentioning about the overflow of the SQL access but cannot find a solution to it so I decide to widen the range of the select statment and delete the entries after the selection is done. But it still does not work.

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 23:04:02 GMT

Hi,

How did you declare the ranges..See example..

RANGES: R_MATNR FOR MARA-MATNR.

DELETE INT_TAB WHERE MATNR NOT IN R_MATNR.

Thanks,

Naren

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 23:04:47 GMT

The ranges table need to be defined with for option , it should not have occurs addition.

Example

Ranges s_vbeln for vbak-vbeln.

Yes there is limitation to use more number of values in where condition , if it exceeds some limit then it will give dump. Also it the data in the internal table exceeds the predefined size in RZ10 then also it dumps.

Pl mark points to all helpful answers and close the thread if problem is solved.

Message was edited by: Imtiaz Ahmed

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 23:06:54 GMT

Hi again,

If you range table has 2k ranges, you should change your delete statement or logic. I have faced similar issues to dumps because of large amount of records in a range internal table being used in a statement such as select, or delete. There is a limitation when the range itab has too many records, don't really know what's the limit, but you could try changing your delete logic.

Hope this helps.

Regards,

Gilberto Li

Re: Delete internal table entries using range table w.multiple ranges

Former Member — Thu, 05 Oct 2006 23:58:51 GMT

Hi Minami,

Why dont you change your delete statement to


delete int_tab where f1 in range_tab

Of course then you'll have to change all your signs to E (exclude)

range_tab-sign = 'E'

The point is now you can divide your range tab into four (500 entries per range tab is ok)


append lines of range_tab from 1 to 500 to range_tab1
append lines of range_tab from 501 to 1000 to range_tab2
... etc ... 
delete int_tab where f1 in range_tab1
delete int_tab where f1 in range_tab2
delete int_tab where f1 in range_tab3
delete int_tab where f1 in range_tab4

Hope this helps

Phil