Re: extracting records using a cursor within a cursor

swoop wrote:
> I am using Oracle 9i
>
> I need to extract records from a table where certain criteria is met
> and then compare 4 fields from those records to 4 fields in another
> table. Where the records don't match I need them extracted into
> another table.
>
> For example.
>
> I want to select *
> >From INVOICE
> Where aa = 1, bb = No, cc=2001;
>
> I then want to compare fields test1, test2, test3 and test4 in table
> INVOICE with fields test1, test2, test3 and test4 in table PAYMENT to
> see if I get a matching record (This tells me if an invoice has been
> paid). If these records do not match then I want the record copied
> from table INVOICE to table NOTPAID.
>
> I understand I need 2 cursors, one to populate variables based on the
> Invoice table and the other based on the Payment table and then do a
> comparison on each. Then to Insert the non matching record from
> Invoice into Notpaid and keep looping until we reach the end of the
> records in the Invoice cursor.

You probably don't even need ONE cursor, just a SQL statement something like:

insert into NOTPAID (col1, col2, ...)
select ... from INVOICE i
where aa = 1, bb = No, cc=2001
and not exists
( select null from PAYMENT p

  where p.test1 = i.test1
  and   p.test2 = i.test2
  and   p.test3 = i.test3
  and   p.test4 = i.test4

SQL is all about dealing with SETS of data, rather than row-by-row processing. Received on Tue May 24 2005 - 07:50:27 CDT