floating error

Question

floating error

hbtkp

SSCommitted

Points: 1731
More actions
April 10, 2012 at 12:37 pm

#253883

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 2

hbtkp (4/10/2012)
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

Come on bro! Forget you dont about this error! I am posting the same question! What is your response?

hbtkp SSCommitted Points: 1731 More actions · Answer 3

the expression is

... (exp(sum(LOG(1+filed/100)))-1)* 100

this cause the error

Lowell SSC Guru Points: 323482 More actions · Answer 4

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 )

SELECT

exp(sum(LOG(1+filed/100))-1)* 100

FROM mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hbtkp SSCommitted Points: 1731 More actions · Answer 5

hbtkp

SSCommitted

Points: 1731

April 10, 2012 at 12:53 pm

#1471440

well if i remove exp ,it works

but i need exp

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

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

hbtkp SSCommitted Points: 1731 More actions · Answer 7

hbtkp

SSCommitted

Points: 1731

April 10, 2012 at 12:58 pm

#1471446

float

SQLRNNR SSC Guru Points: 281324 More actions · Answer 8

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

hbtkp SSCommitted Points: 1731 More actions · Answer 9

hbtkp

SSCommitted

Points: 1731

April 10, 2012 at 1:14 pm

#1471464

do i need to cast it?

how?

Lowell SSC Guru Points: 323482 More actions · Answer 10

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!