Post reply

floating error

  • 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

  • April 10, 2012 at 12:41 pm

    #1471427

    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

    #1471430

    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?

  • April 10, 2012 at 12:43 pm

    #1471431

    the expression is

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

    this cause the error

  • April 10, 2012 at 12:49 pm

    #1471437

    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

    --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!

  • April 10, 2012 at 12:53 pm

    #1471440

    well if i remove exp ,it works

    but i need exp

  • April 10, 2012 at 12:56 pm

    #1471444

    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

    #1471446

    float

  • April 10, 2012 at 1:04 pm

    #1471453

    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

    #1471464

    do i need to cast it?

    how?

  • April 11, 2012 at 9:11 am

    #1471946

    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!

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

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