Using TSQL Cursor

[luis santos]

Hello Comunity

I would like to know if it´s possible to use a TSQL cursor to build a report, like this one :

DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR

select conta,'0-Saldo Anterior/Acumulado' as docnome, u_subconta,' ' as data, '' as mlstamp,
' ' as dinome, ' ' as adoc,0 as dilno,sum(edeb) as edeb,sum(ecre) as ecre from ml (nolock) where ((ml.conta not like '243%' and ml.conta not like '22%') and (ml.conta between '21101' and '26903')) and YEAR(ml.data) = 2007 and (ml.mes < 0 or (ml.mes = 0 and (case when ml.dia = 0 then datepart(dd, ml.data) else ml.dia end)
<1>= 0 and ml.mes <=12 or (ml.mes = 0 and (case when ml.dia = 0 then datepart(dd, ml.data) else ml.dia end)
< 1)) and u_subconta like '50002'
group by conta,docnome,u_subconta,data,
dinome, adoc, dilno, edeb, ecre ,mlstamp
ORDER BY conta, u_subconta,data asc

OPEN RunningTotalCursor

DECLARE @conta varchar(20)
DECLARE @docnome varchar (40)
DECLARE @u_subconta varchar(20)
DECLARE @data datetime
DECLARE @mlstamp varchar(25)
DECLARE @dinome varchar(20)
DECLARE @adoc varchar(20)
DECLARE @dilno varchar(20)
DECLARE @edeb numeric(10,2)
DECLARE @ecre numeric(10,2)


DECLARE @RunningTotal numeric(10,2)
DECLARE @subcontaactual varchar(20)
DECLARE @contaactual varchar(20)

SET @RunningTotal = 0
SET @subcontaactual = ''
SET @contaactual = ''

DECLARE @Results TABLE
(
conta varchar(20),
docnome varchar(40),
u_subconta varchar(20),
data datetime,
mlstamp varchar(25),
dinome varchar(20),
adoc varchar(20),
dilno varchar(20),
edeb numeric(10,2),
ecre numeric(10,2),
RunningTotal numeric(10,2)
)

FETCH NEXT FROM RunningTotalCursor
INTO @conta,@docnome, @u_subconta, @data, @mlstamp,@dinome, @adoc, @dilno, @edeb, @ecre

WHILE @@FETCH_STATUS = 0
BEGIN

if @u_subconta <> @subcontaactual or @conta<>@contaactual
BEGIN
SET @RunningTotal = 0
SET @subcontaactual = @u_subconta
SET @contaactual = @conta
END

SET @RunningTotal = @RunningTotal + (@edeb-@ecre)


--insert values into the results table
INSERT @Results
VALUES (@conta,@docnome, @u_subconta, @data, @mlstamp, @dinome, @adoc, @dilno, @edeb, @ecre,@RunningTotal)

FETCH NEXT FROM RunningTotalCursor
INTO @conta,@docnome, @u_subconta, @data, @mlstamp,@dinome, @adoc, @dilno, @edeb, @ecre
END

CLOSE RunningTotalCursor

DEALLOCATE RunningTotalCursor

--select all records from the results table variable
SELECT *
FROM @Results
ORDER BY conta,u_subconta


Many Thanks

Luis Santos

[admin]

Hi Luis,

It is an interesting problem, and we would like to address it -however, you cant do it directly in the current version. We will review whether it would be possible to implement in a future version.

Just to recap though... You're trying this on SQL server, I assume.. I tried making a very simple (and not at all relevant) example, just for testing purposes. It uses the MSSQL northwind database. It works as a direct execution on SQL server, but not as a Dynamic AI view.

DECLARE MyC CURSOR for select CompanyName from customers
OPEN MyC
DECLARE @TTT TABLE (ccc bigint)
DECLARE @NoNames bigint
DECLARE @NewName varchar(99)
SET @NoNames = 0
FETCH NEXT FROM MyC INTO @NewName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NoNames = @NoNames + 1
FETCH NEXT FROM MyC INTO @NewName
END
Close MyC
DEALLOCATE MyC
Insert into @TTT values(@NoNames)
select * from @TTT

Do you agree in principle that if this simple example could work, then you'd be able to achieve what you need?

Thanks for your input and time

Bo Andersen