topic Re: problem with two open cursor in Application Development and Automation Discussions

problem with two open cursor

Former Member — Tue, 04 Dec 2018 10:36:45 GMT

Hi Expert,

I am using two open Cursor to call the data from two database table then I am trying to compare the data to find out the different data and records between them, but everything was in vain because the first cursor called 10000 records let us say material numbers.. the sound cursor call 10000 material numbers but they are totally different from the first cursor called data.....

The operation is going wrongly as no one of the first 10000 is in the second 10000 although they are in the second database but has not been called in this pass of looping....

I have tried to make append lines to a new intern tables and then to compare them but this is not a solution as I have millions of records and this will be like a select statement....

Please, any advises solving this..

Best Regards

Jenie

Re: problem with two open cursor

matt — Tue, 04 Dec 2018 12:24:36 GMT

SELECT... FROM table1 INTO TABLE first_data_set PACKAGE SIZE 10000.
  SELECT ... FROM table2 INTO TABLE second_data_set 
       FOR ALL ENTRIES IN first_data_set
       WHERE key eq first_data_set-key
  " compare first_data_set with second_data_set.
  ...
ENDSELECT.

No need for a cursor.

Re: problem with two open cursor

RaymondGiuseppi — Tue, 04 Dec 2018 12:54:07 GMT

Well you could try to optimize if the select are 'ordered' (select order by) some pseudocode could be

open cursor 1.
open cursor 2.
do 
  Fetch a package from table 1 into internal table 1
  if no record, exit 
  loop at internal table 1 into record 1.
    while cursor 2 open and second table last value lower than record 1 key
      fetch a package of table2 
      if no record, close cursor 2 and exit 
    endwhile
    compare data with record 1 with internal table 2
  endloop
enddo

Re: problem with two open cursor

RaymondGiuseppi — Tue, 04 Dec 2018 12:55:41 GMT

If you select data from table1 and table with some order by clause, the for all entries could be replaced by some range of data, getting usually much better performance?

SELECT ... FROM table2 INTO TABLE second_data_set 
       WHERE key between table1_min_key and table1_max_key.

Re: problem with two open cursor

matt — Tue, 04 Dec 2018 15:53:45 GMT

Oh yes. A far better solution - combine my answer with yours. i.e. with order, but without cursor.

Re: problem with two open cursor

DoanManhQuynh — Wed, 05 Dec 2018 00:32:04 GMT

this is what i tried to tell her in other post but seem like she need sample code instead :).