Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: extracting records using a cursor within a cursor
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;
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;