stored procedure - cursor help

[saxgeek]

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;

 

[rrrkrishnan]

Do not compile in queryman, compile in BTEQ instead, you will get to see the error messages.

 

[saxgeek]

Thanks - seeing the errors helped somewhat. But the compiler doesn't like the declare cursor statement and I don't know why.

 

[dnoeth]

You need V2R5 to be able to use OPEN CURSOR/FETCH, if you're on 4.1 you need a FOR.
Untested, just cut&paste:

Replace PROCEDURE ncr_temp.spCpaisUpdate( )
BEGIN
-- **********************************************************
-- FILENAME : spCpaisUpdate
-- PURPOSE : UPdates cpaisjoins with new lookup table values
-- **********************************************************
Declare codecolname varchar(50);
Declare codecolval varchar(10);
Declare newnamevalue varchar(250);
Declare dynsql1 varchar(255);

-- no need for temp table ncr_temp.t_lookup_changes_temp

for UpdCpais as
sel a.codecolname, a.codecolvalue, newnamevalue
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
do
call dbc.sysexecsql ('update first_core.t_cpaisjoins from ncr_temp.t_lookup_changes_temp set '
|| :UpdCpais.codecolname || '_name = newnamevalue where '
|| :UpdCpais.codecolname || ' = codecolvalue and codecolname = '
|| :UpdCpais.codecolname || ';');
end for;

-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins
delete from ncr_temp.t_lookup_changes
where joinstablename = 't_cpaisjoins';

END;

Btw, your datamodel mixes data and metadata, this is rather ugly.

Dieter

 

[saxgeek]

Thanks - that worked.