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

Swoop, you are so full of good intentions as your code of mistakes.

Would it not be better if learned a bit about Oracle, SQL and PL/SQL before trying something like this?

 > 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;            -- <<< notice the semicolon
 > ( 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);

These are two statements, the second one does not have a from table with the t alias.

In the PL/SQL, the second cursor does not have a where clause, the fetch into will raise too_many_rows exception.

The insert statement does have a where clause, which is new to me (and perhaps to Oracle).

Those are the points from the belgian jury. Received on Tue May 24 2005 - 10:42:26 CDT