On 24 May 2005 05:50:27 -0700, andrewst_at_onetel.com wrote:
> 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.
If you work in 9i or 10g database meybe will be helpful to look at MERGE
sql statment
Received on Tue May 24 2005 - 08:37:27 CDT
