This discussion is archived
0 Replies Latest reply: Feb 15, 2011 3:37 AM by 839865 RSS

SQL Server Cursor dioe not run in Hyperion Workspace

839865 Newbie
Currently Being Moderated
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

WHILE @@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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points