extracting records using a cursor within a cursor (again)

From: swoop <carl.barrett_at_newcastle.gov.uk>
Date: 31 May 2005 07:18:38 -0700
Message-ID: <1117549118.377852.145030@g43g2000cwa.googlegroups.com>

Hi folks, me again.

I have taken on board what everyone suggested in my post last week and I really appreciate it.

However, and there is always a however - I have been told by my colleague requesting this to use 2 cursors. 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.

THE TABLE THAT IS TO HAVE THE RECORDS EXTRACTED TO IS NOT TO BE OVERWRITTEN. EVERYTIME THIS PROCEDURE/CODE IS RAN ONLY RECORDS THAT HAVE NOT BEEN CHECKED SHOULD BE ADDED TO THE NEW TABLE. As far as 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.

Any guidance and example code of something similar would be greatly appreciated. Here is code I have started with.

DECLARE CURSOR tgb_cur IS

SELECT	cmpy, supp, entry_date, sysref
FROM	tgbtran
WHERE	tran_type = 1
AND	ordr_rltd_flag = 'Y'
AND	yy = 2005;

v_cmpy			tgbtran.cmpy%type;
v_supp			tgbtran.supp%type;
v_entry_date		tgbtran.entry_date%type;
v_sysref		tgbtran.sysref%type;

v_py_cmpy		tgbpaym.cmpy%type;
v_py_supp		tgbpaym.supp%type;
v_py_tran_entry_date	tgbpaym.tran_entry_date%type;
v_py_tran_sysref	tgbpaym.tran_sysref%type;

CURSOR	tgbp_cur IS

SELECT	cmpy, supp, tran_entry_date, tran_sysref
FROM	tgbpaym
WHERE	v_cmpy <> v_py_cmpy
AND	v_supp <> v_py_supp
AND	v_entry_date <> v_py_tran_entry_date
AND	v_sysref <> v_py_tran_sysref;

BEGIN OPEN tgb_cur;
LOOP

	FETCH tgb_cur INTO	v_cmpy,
				v_supp,
				v_entry_date,
				v_sysref;
	EXIT WHEN tgb_cur%notfound;


	OPEN tgbp_cur;
		FETCH tgbp_cur INTO	v_py_cmpy,
					v_py_supp,
					v_py_tran_entry_date,
					v_py_tran_sysref;

		INSERT INTO	utgbtran
		VALUES		(v_cmpy, v_supp, v_entry_date, v_sysref);

	CLOSE tgbp_cur;

END LOOP;
CLOSE tgb_cur;
END;
/ Received on Tue May 31 2005 - 09:18:38 CDT