Re: extracting records using a cursor within a cursor (again)

swoop wrote:
> CURSOR tgbp_cur IS
> SELECT cmpy, supp, tran_entry_date, tran_sysref
> FROM tgbpaym
> WHERE v_cmpy <> v_py_cmpy
> AND v_supp <> v_py_supp
> AND v_entry_date <> v_py_tran_entry_date
> AND v_sysref <> v_py_tran_sysref;

This cursor doesn't check that there is NO MATCH for those values in tgbpaym, it finds ALL the rows that do not match those values, whether there IS a row that matches or not. Can you not see the difference? e.g. "select * from emp where ename != 'KING';" returns 13 rows, none of them having ename='KING'. But that doesn't mean there isn't a 'KING' in the table (there is).

The cursor should be:

 CURSOR	tgbp_cur IS
 SELECT	cmpy, supp, tran_entry_date, tran_sysref
 FROM	tgbpaym
 WHERE	v_cmpy = v_py_cmpy
 AND	v_supp = v_py_supp
 AND	v_entry_date = v_py_tran_entry_date
 AND	v_sysref = v_py_tran_sysref;

Then after the FETCH you can use tgbp_cur%NOTFOUND to see if there was no match.

BTW, has your colleague divulged his/her reasons for insisting on doing this in such an inefficient manner yet? Received on Tue May 31 2005 - 09:39:30 CDT