Forum Stats

  • 3,854,261 Users
  • 2,264,344 Discussions
  • 7,905,624 Comments

Discussions

SQL Server Cursor dioe not run in Hyperion Workspace

Hi all,

I have a bqy file which runs fine in the design studio client (v9.3) however when I import it into the Workspace and try and run it i get the following error :

An Interactive Reporting Service error has occurred.-SQL API: [SQLFetchScroll], SQL RETURN: [-1], SQL STATE: [HY010], SQL NATIVE ERROR: [0], SQL MESSAGE: [[DataDirect][ODBC lib] Function sequence error]
(2001)

The query was built from importing the SQL as below:

DECLARE @Fig1 int
DECLARE @i int

DECLARE skuCursor CURSOR FOR
Select r.QtyReceived / b.qty as Figure1
from ReceiptDetail r
inner join BillofMaterial b on b.SKU = r.Lottable03
where r.DateReceived > getdate() - 14

create table #TEMPIG (Test char(2),Median1 int)

OPEN skuCursor
FETCH NEXT FROM skuCursor INTO @Fig1

set @i = 0

@FETCH_STATUS = 0
BEGIN
while @i < @Fig1
begin
insert into #TempIG (Test,Median1) values ('MV',@Fig1)
--print @Fig1
set @i = @i + 1

end
set @i = 0
FETCH NEXT FROM skuCursor INTO @Fig1
END

CLOSE skuCursor
DEALLOCATE skuCursor

--select cast(Median1 as Int) from #TEMPIG
--order by 1 asc

select
AVG(Median1) as MedianVal
from
(
select Median1,
ROW_NUMBER() over (partition by Test order by Median1 ASC) as MedRank,
COUNT(*) over (partition by Test) as MedCount
from
#TEMPIG
) x
where
x.MedRank in (x.MedCount/2+1, (x.MedCount+1)/2)

drop table #TEMPIG

Could anyone explain what is wrong and how to put it right.

Many Thanks in advance.

Ian.
This discussion has been closed.