I'm trying to compile a stored proc which contains a cursor. When I compile the procedure in queryman, it doesn't give me error messages. Here the procedure - any help would be appreciated:
Replace PROCEDURE ncr_temp.spCpaisUpdate( )
BEGIN
-- **********************************************************
-- FILENAME : spCpaisUpdate
-- PURPOSE : UPdates cpaisjoins with new lookup table values
-- **********************************************************
lab1: BEGIN
BEGIN
Declare codecolname varchar(50);
Declare codecolval varchar(10);
Declare newnamevalue varchar(250);
Declare dynsql1 varchar(255);
Declare UpdCpais Cursor for
Select codecolname, codecolvalue, newnamevalue
from ncr_temp.t_lookup_changes_temp FOR READ;
END;
-- get rows that apply only to t_cpaisjoins table
BEGIN
insert into ncr_temp.t_lookup_changes_temp
sel a.codecolname, a.codecolvalue, newnamevalue, effectivedate
from ncr_temp.t_lookup_changes a
,(sel codecolname, codecolvalue, max(effectivedate) as lastupddate
from ncr_temp.t_lookup_changes
group by 1,2) b
where joinstablename = 't_cpaisjoins'
and a.codecolname = b.codecolname
and a.codecolvalue = b.codecolvalue
and a.effectivedate = b.lastupddate;
-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins
delete from ncr_temp.t_lookup_changes
where joinstablename = 't_cpaisjoins';
Open cursor UpdCpais;
While (sqlcode = 0) Do
Fetch next from UpdCpais into
:codecolname, :codecolval, :newnamevalue;
call dbc.sysexecsql ('update first_core.t_cpaisjoins from ncr_temp.t_lookup_changes_temp set '
|| :codecolname || '_name = newnamevalue where ' || :codecolname || ' = codecolvalue and codecolname = '
|| :codecolname || ';');
End while;
Close UpdCpais;
-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins
delete from ncr_temp.t_lookup_changes_temp all;
END;
END lab1;
END;
Replace PROCEDURE ncr_temp.spCpaisUpdate( )
BEGIN
-- **********************************************************
-- FILENAME : spCpaisUpdate
-- PURPOSE : UPdates cpaisjoins with new lookup table values
-- **********************************************************
lab1: BEGIN
BEGIN
Declare codecolname varchar(50);
Declare codecolval varchar(10);
Declare newnamevalue varchar(250);
Declare dynsql1 varchar(255);
Declare UpdCpais Cursor for
Select codecolname, codecolvalue, newnamevalue
from ncr_temp.t_lookup_changes_temp FOR READ;
END;
-- get rows that apply only to t_cpaisjoins table
BEGIN
insert into ncr_temp.t_lookup_changes_temp
sel a.codecolname, a.codecolvalue, newnamevalue, effectivedate
from ncr_temp.t_lookup_changes a
,(sel codecolname, codecolvalue, max(effectivedate) as lastupddate
from ncr_temp.t_lookup_changes
group by 1,2) b
where joinstablename = 't_cpaisjoins'
and a.codecolname = b.codecolname
and a.codecolvalue = b.codecolvalue
and a.effectivedate = b.lastupddate;
-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins
delete from ncr_temp.t_lookup_changes
where joinstablename = 't_cpaisjoins';
Open cursor UpdCpais;
While (sqlcode = 0) Do
Fetch next from UpdCpais into
:codecolname, :codecolval, :newnamevalue;
call dbc.sysexecsql ('update first_core.t_cpaisjoins from ncr_temp.t_lookup_changes_temp set '
|| :codecolname || '_name = newnamevalue where ' || :codecolname || ' = codecolvalue and codecolname = '
|| :codecolname || ';');
End while;
Close UpdCpais;
-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins
delete from ncr_temp.t_lookup_changes_temp all;
END;
END lab1;
END;