April 10, 2012 at 12:37 pm
hi
i am using 1 expression in my tsql and getting error like
Arithmetic overflow error converting expression to data type float.
what is the reason,what should i do
April 10, 2012 at 12:41 pm
Can you provide the query?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 10, 2012 at 12:42 pm
hbtkp (4/10/2012)
hii am using 1 expression in my tsql and getting error like
Arithmetic overflow error converting expression to data type float.
what is the reason,what should i do
Come on bro! Forget you dont about this error! I am posting the same question! What is your response?
April 10, 2012 at 12:43 pm
the expression is
... (exp(sum(LOG(1+filed/100)))-1)* 100
this cause the error
April 10, 2012 at 12:49 pm
well, after correcting the extra parenthesis in your example, i'm not seeing the specific issue why it might overflow;
possibly the table has a huge number of rows?
the field "filed" is what datatype? is it a car/varchar? maybe one row in your datais not numeric?
with mySampleData(filed)
AS
( SELECT 2147483647 UNION ALL
SELECT 2147483647 UNION ALL
SELECT 2147483647 UNION ALL
SELECT 2147483647 UNION ALL
SELECT 2147483647 )
SELECT
exp(sum(LOG(1+filed/100))-1)* 100
FROM mySampleData
Lowell
April 10, 2012 at 12:53 pm
well if i remove exp ,it works
but i need exp
April 10, 2012 at 12:56 pm
What is the datatype of filed?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 10, 2012 at 12:58 pm
float
April 10, 2012 at 1:04 pm
Sounds like you have a value in your table that when combined with the exp function you exceed the bounds of the float datatype.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 10, 2012 at 1:14 pm
do i need to cast it?
how?
April 11, 2012 at 9:11 am
you said if you remove the exp() it works.
what value is returned by sum(log(CASE WHEN field = 0.0 THEN 1 WHEN field IS NULL THEN 1 WHEN field= -100 THEN 1 ELSE 1+field/100.0 END )) )-1.0
whatever that value is, it causes the overflow when you exp() it.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply