0 Replies Latest reply: Feb 15, 2011 5:37 AM by 839865 RSS

    SQL Server Cursor dioe not run in Hyperion Workspace

    839865
      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.