Re: extracting records using a cursor within a cursor

swoop wrote:
> 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;
> /

Why would you use a cursor loop to do this?

A simple SQL statement would suffice and even if you did require a processing loop a cursor would be the worst possible solution: Look up bulk collection and FORALL.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)