RE: Updatable Cursor "Hangs" on last fetch

Terry:

<snip>
[Original code:]
     C/EXEC SQL Declare CBI  Cursor  For
     C+ select <various fields>
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B'and ab56trgact = 'U'
     C+     For update

[Suggestion from Charles Wilt:]
     C/EXEC SQL
     C+ delete
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B'and ab56trgact = 'U' 
     C/END-EXEC

Some background info:  This program compares a pair of records, checks
if there are the same, and deletes them if they are.  99.9% of records
will be deleted.
</snip>

If the logic is truly just to delete the record if it is also found in
another file, then you could just do:

     C/EXEC SQL
     C+ delete
     C+     from HS0101
     C+     where AB56TRGDAT >=  :fromdate
     C+          and AB56TRGDAT <= :todate
     C+          and AB56TRGBA = 'B' and ab56trgact = 'U' 
==>  C+          and exists (
==>  C+                select keyfld_b1, keyfld_b2
==>  C+                  from otherfile
==>  C+                 where keyfld_a1 = keyfld_b1
==>  C+                   and keyfld_a2 = keyfld_b2)  (repeat as needed)
     C/END-EXEC

Just a thought,
--Bruce Guetzkow