creating a total with variables with a looping cursor

[mhr]

I'm trying to loop through a table using a cursor and assigning the values from each row to variables to come up with two grand totals. I'm adding itself plus the calculation to itself to get a grand total variable. There are about 5 record that match the criteria of the cursor. I can view the individual rows values but when i try to utilize adding it to itself to have a grand total variable its passing me a null value. Can anyone tell me what i'm doing wrong. It it looks like it would total the values up but its not. Any help would be appreciated.
Mark

OPEN cursorPur
FETCH NEXT FROM cursorPur into @strCUSTID,@strCUSTNAME,
@strCITY,@strSTATE,@dtORDDT,@intQTY,@decDISC,@decU
PRICE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @decTOTPAID = @decTOTPAID + ((@intQTY * @decUPRICE) - @decDISC)
SET @intTOTQTY = @intTOTQTY + @intQTY
FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME,
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE
END
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOTPAID AS TOTPAID,
@intTOTQTY AS TOTALQTY
CLOSE cursorPur
DEALLOCATE cursorPur

[Claire]

Try this.Declare 2 new variable for getting the result of your grand totals.
(I dont know which datatype you use,I assume both are int)


declare @decTOT int
declare @intTOT int
set @decTOT = 0
set @intTOT = 0

declare cursorPur cursor for select ****

OPEN cursorPur
FETCH NEXT FROM cursorPur into @strCUSTID,@strCUSTNAME,
@strCITY,@strSTATE,@dtORDDT,@intQTY,@decDISC,@decU PRICE
WHILE @@FETCH_STATUS = 0
BEGIN

SET @decTOT = @decTOT+
@decTOTPAID + ((@intQTY * @decUPRICE) - @decDISC)

SET @intTOT = @intTOT+
@intTOTQTY + @intQTY

FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME,
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE
END
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,

@decTOT AS TOTPAID,

@intTOT AS TOTALQTY

CLOSE cursorPur
DEALLOCATE cursorPur

[mhr]

It still would not work with the calculations in the loop it seems it will only add the two varibles together with no calculations.
I did the calculations for the select in the cursor? I works fine now.

I guess we're not allowed to do calculations with loop variables!!!

WHILE @@FETCH_STATUS = 0
BEGIN
SET @decTOT = @decTOT + @decTOTPAID
SET @intTOT = @intTOT + @intTOTQTY

FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME,
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE, @decTOTPAID, @intTOTQTY

END
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOT AS TOTPAID,
@intTOT AS TOTALQTY

CLOSE cursorPur
DEALLOCATE cursorPur

[Claire]

mhr,

We do allowed to do calculation inside cursor.I made some mistake in the original post.I thought @decTOTPAID is one of the fetched value from cursor you declared.

In this case,please ignore my @decTOT ,just use your @decTOTPAID.Because they both are the same.

Since I cant imagine how your tables and datas looked like.I tried to demonstrate a cursor which can do calculation including + , * and -.
The following is my simulation.
------------------------------------------------------------------------------------------------------------------------

1.Create a simulating table with various columns

create table test (v1 int,v2 int,v3 int,v4 int)

2.inserting values

insert into test select 5,4,5,4
insert into test select 6,5,6,5
insert into test select 7,6,7,6
insert into test select 8,7,8,7
insert into test select 9,8,9,8

3.Cursor

declare @v1 int
declare @v2 int
declare @v3 int
declare @v4 int
declare @var1 int set @var1 = 0
declare @var2 int set @var2 = 0
declare @var3 int set @var3 = 0

declare cursor1 cursor for select v1,v2,v3,v4 from test
open cursor1
fetch next from cursor1 into @v1,@v2,@v3,@v4
while @@fetch_status = 0
begin
SET @var1 = @var1+((@v1 * @v2) - @v3)
print 'var1 is:'+convert(varchar,@var1)

SET @var2 = @var2+@v4
print 'var2 is:'+convert(varchar,@var2)

set @var3 = @var1*@var2+@var3
print 'var3 is:'+convert(varchar,@var3)

fetch next from cursor1 into @v1,@v2,@v3,@v4

end

4.close cursor1
deallocate cursor1

5.The results display:

var1 is:15
var2 is:4
var3 is:60
var1 is:39
var2 is:9
var3 is:411
var1 is:74
var2 is:15
var3 is:1521
var1 is:122
var2 is:22
var3 is:4205
var1 is:185
var2 is:30
var3 is:9755
------------------------------------------------------------------------------------------------------------------------

I already add print statement inside the cursor.this will show you each result of every loop.

[PinkPanther2003]

Again I have to butt in and ask the Orignal Poster -

"Why do something using a cursor when you can do it using just plain 'ol SQL?"

Or is the query so complex you have to use a cursor?

Usually (I won't say always cause I've been proved wrong about SQL vs cursors before, in Access at least) using SQL is much faster than using a cursor, especially if the table is indexed properly.

HTH,

Peter

[mhr]

I have an instructor that is very persistant about us utilizing every naming convention and in this example he wanted us to use a cursor. Other than that i agree with you. I would not have used a cursor for something as simple as this. The part of the code I have displayed is only a small part of what I had to do.
Mark