Help with cursor and decimal values

The following table when using a cursor gives 0 for decimals where the value
s
are below 1. I am trying to use this to apply a factor and always get a zero
if the factor below 1, as a result my logic fails.
Can some one help me on this?
-- ========== Table & data =============
CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
INSERT INTO TestFactor VALUES (1, 0.25)
INSERT INTO TestFactor VALUES (2, 0.50)
INSERT INTO TestFactor VALUES (3, 0.75)
INSERT INTO TestFactor VALUES (4, 0.125)
INSERT INTO TestFactor VALUES (5, 0.25)
INSERT INTO TestFactor VALUES (6, 2)
INSERT INTO TestFactor VALUES (7, 1)
-- ========== Table & data =============
DECLARE @.Factor decimal
DECLARE my CURSOR
FOR SELECT FactorValue from TestFactor
OPEN my
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
END
CLOSE my
DEALLOCATE my
Thankschange DECLARE @.Factor decimal to
DECLARE @.Factor decimal (5,2) -- same as in the table
http://sqlservercode.blogspot.com/|||Change
DECLARE @.Factor decimal
to
DECLARE @.Factor decimal(5,2)
"Ram" wrote:

> The following table when using a cursor gives 0 for decimals where the val
ues
> are below 1. I am trying to use this to apply a factor and always get a ze
ro
> if the factor below 1, as a result my logic fails.
> Can some one help me on this?
> -- ========== Table & data =============
> CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
> INSERT INTO TestFactor VALUES (1, 0.25)
> INSERT INTO TestFactor VALUES (2, 0.50)
> INSERT INTO TestFactor VALUES (3, 0.75)
> INSERT INTO TestFactor VALUES (4, 0.125)
> INSERT INTO TestFactor VALUES (5, 0.25)
> INSERT INTO TestFactor VALUES (6, 2)
> INSERT INTO TestFactor VALUES (7, 1)
> -- ========== Table & data =============
>
> DECLARE @.Factor decimal
> DECLARE my CURSOR
> FOR SELECT FactorValue from TestFactor
> OPEN my
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> END
> CLOSE my
> DEALLOCATE my
> Thanks
>|||Thanks Mark & SQL
I was going crazy on this. I think I need a vacation
"Mark Williams" wrote:
> Change
> DECLARE @.Factor decimal
> to
> DECLARE @.Factor decimal(5,2)
> "Ram" wrote:
>

Help with a query please

I have been strugling with for hours -
I have a table with the following columns
UID(decimal) | SD(bit) | MB(bit) | TS(bit) | Total(Decimal)
I want to run a query which will return
UID, Total if SD is True AND
UID, Total if MB is True AND
UID, Total if TS is True
UID is the userID so there would be a WHERE statement
ORDERED BY Total Descending
Many thanks in advance
Many thanks,
Rob
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:9137
On Sun, 12 Mar 2006 03:17:27 -0800, RobA wrote:

>I have been strugling with for hours -
>I have a table with the following columns
>UID(decimal) | SD(bit) | MB(bit) | TS(bit) | Total(Decimal)
>I want to run a query which will return
>UID, Total if SD is True AND
>UID, Total if MB is True AND
>UID, Total if TS is True
>
>UID is the userID so there would be a WHERE statement
>ORDERED BY Total Descending
>Many thanks in advance
Hi Rob,
It's hard to tell exactly what you need from this description. If the
query below is not what you need, then please read www.aspfaq.com/5006
to find a better way to describe your problem.
SELECT UID,
SUM(CASE WHEN SD = CAST(1 AS bit) THEN Total ELSE 0 END),
SUM(CASE WHEN MB = CAST(1 AS bit) THEN Total ELSE 0 END),
SUM(CASE WHEN TS = CAST(1 AS bit) THEN Total ELSE 0 END)
FROM YourTable
GROUP BY UID
Hugo Kornelis, SQL Server MVP