Re: extracting records using a cursor within a cursor

From: <andrewst_at_onetel.com>
Date: 25 May 2005 05:13:41 -0700
Message-ID: <1117023220.997008.284890@g49g2000cwa.googlegroups.com>

swoop wrote:
> 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.

You would still only need ONE cursor, which would look something like this:

DECLARE
  CURSOR c IS
  SELECT CMPY, SUPP, ENTRY_DATE, SYSREF   FROM tgbtran t
  WHERE tran_type = 1

  AND     ordr_rltd_flag = 'Y'
  AND     yy = 2005

  AND NOT EXISTS
  ( 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);

BEGIN
  FOR r IN c LOOP
    INSERT INTO utgbtran (...)
    VALUES (r.CMPY, r.SUPP, r.ENTRY_DATE, r.SYSREF);   END LOOP;
END; Received on Wed May 25 2005 - 07:13:41 CDT