Re: extracting records using a cursor within a cursor

From: <andrewst_at_onetel.com>
Date: 24 May 2005 09:24:37 -0700
Message-ID: <1116951877.538529.88340@f14g2000cwb.googlegroups.com>

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);

Well almost...

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

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);

It is all one SQL statement - only one semi-colon, right at the end. Received on Tue May 24 2005 - 11:24:37 CDT