Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

stored procedure - cursor help

Status
Not open for further replies.

saxgeek

Programmer
May 2, 2003
15
US
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;
 
Do not compile in queryman, compile in BTEQ instead, you will get to see the error messages.
 
Thanks - seeing the errors helped somewhat. But the compiler doesn't like the declare cursor statement and I don't know why.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top