Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seware
Starting Member
11 Posts |
 Posted - 2011-06-29 : 18:07:41
|
| I'm a noob at complex SQL... having only done simple joining and such for the past few years. Now I'm having to update a UDF that someone else wrote with some new values.This snippet below is giving me an overlow error and I don't know why. The result is a percentage (that can't exceed 99.9999%) that should easily fit in a decimal(6,4). And the SUM portion of the calcs will fit in the decimal(16,4). Is there something under the covers I'm unaware of?The formula is:(SUM((FILORD - FILORDOUT) / FILORD)) / @iDaysWithOrders * 100(BTW if someone can suggest a nicer way to do this, please advise)coalesce(case when @iDaysWithOrdersYTD = 0 then 0 else(SUM(case when dt.FILORD = 0 then 0 else ((CAST(dt.FILORD AS decimal(16,4)) - CAST(dt.FILORDOUT AS decimal(16,4))) / CAST(dt.FILORD AS decimal(16,4)))end )/ CAST(@iDaysWithOrdersYTD AS decimal(16,4)) * CAST(100 AS decimal(16,4)))end, 0) AS decimal(6,4)) AS FirstContactFillRatePerOrderYTD |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-29 : 19:27:43
|
If the result is more than 99.9999, for example 99.99999, then that will round off to 100.0000 and cause an overflow error.DECLARE @j DECIMAL(6,4);SET @j = 99.9999; -- no error.SET @j = 99.99995; -- Arithmetic overflow error converting numeric to data type numeric. But since you say that the result cannot exceed 99.9999, it could be something else.To test, instead of casting to decimal, cast to float and examine the results you get. |
 |
|
|
seware
Starting Member
11 Posts |
Posted - 2011-06-30 : 08:41:26
|
| Maybe I'm not understanding... Since my arithmetic within the function uses 16,4, the output cannot exceed 16,4 can it? If my assumption is correct, and the answer is always less than 100.0000, it should fit in a 6,4, right? I'll test with the float, but I'd like to "understand" so I cannot repeat the same errors.Thanks sunitabeck. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-30 : 08:55:42
|
Since I don't have information on the data that is in your table, I am unable to say specifically why it is overflowing, but the number you are casting as decimal(6,4) is greater than 100. (It could also be that the numbers you are casting as decimal(16,4) are too large, but unlikely unless you have really some bad data).My suggestion to cast it to float was so you can see the results to examine if they are more than 100. Here is an example - which uses the exact same query you have using cooked up values that I inserted. The number that is cast to decimal(6,4) in this case is 300, and so it fails.CREATE TABLE #tmp (FILORD INT, FILORDOUT INT);INSERT INTO #tmp VALUES(100000,-200000);DECLARE @iDaysWithOrdersYTD INT; SET @iDaysWithOrdersYTD = 1;SELECT CAST (COALESCE( CASE WHEN @iDaysWithOrdersYTD = 0 THEN 0 ELSE ( SUM( CASE WHEN dt.FILORD = 0 THEN 0 ELSE ( ( CAST(dt.FILORD AS DECIMAL(16, 4)) - CAST(dt.FILORDOUT AS DECIMAL(16, 4)) ) / CAST(dt.FILORD AS DECIMAL(16, 4)) ) END ) / CAST(@iDaysWithOrdersYTD AS DECIMAL(16, 4)) * CAST(100 AS DECIMAL(16, 4)) ) END, 0 ) AS DECIMAL(6, 4)) AS FirstContactFillRatePerOrderYTDFROM #tmp dt;drop table #tmp; |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-07-01 : 14:11:06
|
quote:
Originally posted by seware
(BTW if someone can suggest a nicer way to do this, please advise)
Can you provide the datatypes? I'm guessing you don't need to do as much CASTing, but hard to say..One shortcut is to get rid of the CASE and use a NULLIF function. Something like:COALESCE( (SUM((FILORD - FILORDOUT) / FILORD)) / NULLIF(@iDaysWithOrders, 0) * 100 ) |
|
|
|
|
|
|
|
|
overflow issues