Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Arithmetic overflow error converting numeric to data type numeric

    Guys

    I'm getting the above when trying to populate a variable. The values in question are :
    @N = 21
    @SumXY = -1303765191530058.2251000000
    @SumXSumY = -5338556963168643.7875000000

    When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
    When I try the following ie to populate a variable with that value I get the error -
    SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)



    Any ideas ??

  2. #2
    Join Date
    Oct 2005
    Posts
    37
    Sorry - did a typo - problem code is
    SELECT @R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)
    not
    SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try:
    SELECT R2Top = CAST((@N * @SumXY) - (@SumXSumY * @SumXSumY) as NUMERIC (38, 10))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2005
    Posts
    37
    Thanks blindman but still same error.

    If I change data type of @R2Top to FLOAT I get no error but aren't there accuracy issues with FLOAT ? I have further calculations to perform in the code and the accuracy is very important

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What are the datatypes for @N, @SumXY, and @SumXSumY?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK. Here is your problem. numeric(38, 10) only leaves you 28 digits to the left of the decimal. Your problem requires 32 digits to the left of the decimal.

    This code works:
    Code:
    declare	@N int
    declare	@SumXY decimal(26, 10)
    declare	@SumXSumY decimal(26, 10)
    declare	@R2Top numeric(38, 6)
    
    set @N = 21
    set @SumXY = -1303765191530058.2251000000
    set @SumXSumY = -5338556963168643.7875000000
    
    select @R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)
    select	@R2Top
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2005
    Posts
    37
    Thanks blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •