Sybase problems 2

Cursor - Not fetching next record
Posted: Apr 13, 2007 5:45 PM Reply


Hi,
I am BusinessObjects Developer and new to Sybase-IQ.
I had created on stored procedure and facing an issue that the cursor is not fetching next record.
Can anybody help to debug this issue.

Below is the stored procedure.
declare @catcode varchar(12);
declare @MX integer;
declare @MTH integer;
declare @Yr integer;
declare @Yr_Max integer;
declare @Yr_Min integer;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
if sqlstate = err_notfound then
print sqlstate;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
close CRS_catcode
end
end

-------------------------------------------------------------------------------------------------------------
Re: Cursor - Not fetching next record
Posted: Jul 12, 2007 5:34 PM Reply


hi dear
u have not use fetch next statement in your sp before the close of the cursor

try this , i think it will help u


/* Notice: Formatted SQL is not the same as input */
(52,32)then
DECLARE @catcode VARCHAR(12);
DECLARE @MX INTEGER;
DECLARE @MTH INTEGER;
DECLARE @Yr INTEGER;
DECLARE @Yr_Max INTEGER;
DECLARE @Yr_Min INTEGER;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;

set @Yr=@Yr+1
end loop
end if;

IF sqlState = Err_NotFound
IF sqlState = Err_NotFound Then
PRINT sqlState;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
----------------------------------------------
fetch next CRS_catcode
into @catcode ;
-----------------------------------------------
end

close CRS_catcode

end