Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: extracting records using a cursor within a cursor

Re: extracting records using a cursor within a cursor

From: swoop <carl.barrett_at_newcastle.gov.uk>
Date: 24 May 2005 08:17:26 -0700
Message-ID: <1116947846.345538.165880@g44g2000cwa.googlegroups.com>


I have tried the above with my code but get an error on the last line telling me t.sysref is an invalid column name.

INSERT INTO utgbtran
VALUES (CMPY, SUPP, ENTRY_DATE, SYSREF);

SELECT	CMPY, SUPP, ENTRY_DATE, SYSREF
FROM	tgbtran t
WHERE	tran_type = 1
AND	ordr_rltd_flag = 'Y'
AND	yy = 2005;

( select null from TGBPAYM p
  where p.cmpy = t.cmpy
  and   p.supp = t.supp
  and   p.trans_entry_date = t.entry_date
  and   p.trans_sysref = t.sysref);

Here is my attempt with 2 cursors but I can't get it to work. I know you suggested I didn't need to use cursors but for my own benefit I would like to get it to work with cursors and your method so I can see it work from different angles as I am new to all this.

Any help will be appreciated.

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;




CURSOR	tgbp_cur IS

SELECT	cmpy, supp, trans_entry_date, trans_sysref
FROM	tgbpaym;

v_py_cmpy		tgbpaym.cmpy%type;
v_py_supp		tgbpaym.supp%type;
v_py_trans_entry_date	tgbpaym.trans_entry_date%type;
v_py_trans_sysref	tgbpaym.trans_sysref%type;

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_trans_entry_date,
					v_py_trans_sysref;

		INSERT INTO	utgbtran
		VALUES		(v_cmpy, v_supp, v_entry_date, v_sysref);
		WHERE		v_cmpy <> v_py_cmpy
		AND		v_supp <> v_py_supp
		AND		v_entry_date <> v_py_trans_entry_date
		AND		v_sysref <> v_py_trans_sysref;

	CLOSE tgbp_cur;

END LOOP
CLOSE tgb_cur;
END;
/ Received on Tue May 24 2005 - 10:17:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US