May 25, 2016 at 9:35 am
Hi
Can anyone tell me how to prevent arithmetic overflow on a percentage calc? I know this is probably really easy, but I am tearing my hair out.
The portion of the update is:
CASE
WHEN (o.RATINGtYPE = 4 AND Su.Cat = 'Supply') THEN
CAST(CAST(ISNULL(SU.Num, 0) AS DECIMAL(5,2))/CAST(o.Num AS DECIMAL(5,2)) * 100 AS DECIMAL(5,2)) ELSE [SatisfiedPercentage]
END
the 'num' fields are integer values from a cte which counts rows. No value is over 140, and the o.num is always bigger or equal to the Su.num, so the percentage is never greater than 100%
the results get updated to a table, and the target field is also decimal(5,2) (I get the error even when selecting anyway)
thanks in advance
🙂
May 25, 2016 at 9:42 am
DECIMAL(5,2) can store 5 total digits. 140 * 100 is 140000, or 6 digits.
Try using bigger decimals in the equation then casting final output to 5,2.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2016 at 10:22 am
Hi, thanks for replying. I am getting a bit further. I have changed it to:
CAST((CAST(ISNULL(SU.Num, 0) AS Decimal(10,4))/CAST(o.Num AS Decimal(10,4)) * 100 )AS Decimal(5,2) ) ELSE [SatisfiedPercentage]
When I construct it into a select statement, I get the answers back as a nn.nn percentage, no errors. However, I still get the error when trying to update the table. Maybe I should just change the table too? Not sure why I should need to go it though.
May 25, 2016 at 10:39 am
I can't see why that would fail when you insert the output into a table that has decimal(5,2) for that column's data type.
Try doing a SELECT INTO... and seeing if that works (and what the created column data type is).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2016 at 11:12 am
What is the data type of "[SatisfiedPercentage]"? Because it's also part of the same CASE statement.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 25, 2016 at 12:23 pm
The column is a decimal(5,2) -- it is actually the target column.. it updates to its own value if the case statement is not capturing a change for that scenario.
I will try a select into..
thank you both for responding.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply