Re: extracting records using a cursor within a cursor

From: swoop <carl.barrett_at_newcastle.gov.uk>
Date: 25 May 2005 04:52:21 -0700
Message-ID: <1117021941.674855.110960@f14g2000cwb.googlegroups.com>

Hi,

I have managed to correct my error on the SQL statement to get it working, so thanks very much for your help. It has updated my TGBPAYM table wit the required records.

However, I have been asked to do it using cursors as I will need to add more to it later as this is just the start. If someone could point/guide me as to where I have gone wrong with it and would really appreciate it.

I have made some changes to it but not sure if it's the right direction.

Cheers.

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 Wed May 25 2005 - 06:52:21 CDT