I don't use cursors myself, but are you sure "open cur_list_of_contracts;"
is enough to set a value different from 0 for row_count? Maybe you need to
do the first fetch earlier, e.g.
execute block as
declare contractno CHAR(20);
declare cur_list_of_contracts cursor for (select CU.CONTRACTNO
from CLASSIC_UPGRADE CU
join CONTRACT C on C.CONTRACTNO
= CU.CONTRACTNO
where C.CONTRACTSTATUS = 'A'
and CU.INSERTDATE >
'09/01/2016'
and CU.INSERTDATE <
'09/10/2016'
and (CU.STATUS in
('authorized', 'Authorized')
and C.CONVERT2YRDATE IS NULL
);
begin
open cur_list_of_contracts;
fetch cur_list_of_contracts into contractno;
while (ROW_COUNT > 0) do
begin
UPDATE CONTRACT C
SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU
where CU.CONTRACTNO = :contractno)
where C.CONTRACTNO = :contractno;
if (ROW_COUNT = 0) then leave;
fetch cur_list_of_contracts into contractno;
end
close cur_list_of_contracts;
end;
HTH,
Set
2017-02-17 10:04 GMT+01:00 Vishal Tiwari [email protected]
[firebird-support] <[email protected]>:
>
>
> Hi All,
>
> I have created the below cursor on Firbird DB (version 2.5). The update
> statement in below cursor is not updating value.
>
> *execute block*
> *as *
> *declare contractno CHAR(20);*
> *declare cur_list_of_contracts cursor for (Select CU.CONTRACTNO From
> CLASSIC_UPGRADE CU, CONTRACT C where C.CONTRACTNO = CU.CONTRACTNO AND
> C.CONTRACTSTATUS = 'A' AND CU.INSERTDATE > '09/01/2016' AND CU.INSERTDATE <
> '09/10/2016' AND (CU.status = 'authorized' or CU.status = 'Authorized') AND
> C.CONVERT2YRDATE IS NULL );*
>
> *begin*
> *open cur_list_of_contracts;*
> * while (ROW_COUNT > 0) do*
> * begin*
> * fetch cur_list_of_contracts into contractno;*
> * UPDATE CONTRACT C*
> * SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU
> where CU.CONTRACTNO = :contractno)*
> * where C.CONTRACTNO = :contractno;*
>
> * if (ROW_COUNT = 0) then leave;*
> * suspend;*
> * end*
> * close cur_list_of_contracts;*
> *end;*
>
>
> The block is getting executed without any errors.
>
> But, the output is not as expected. The records in the table are not
> getting updated.
>
>
> With Best Regards.
>
> Vishal
>
>
>
>
