Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Exclamation Unanswered: Arithmetic overflow error converting expression to data type int

    Under certain circumstances I am getting the following error

    "Arithmetic overflow error converting expression to data type int"

    when running the following code:

    SELECT Count(*), Sum(GrossWinAmount)
    FROM LGSLog
    WHERE
    (CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')

    If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first suggestion would be:
    Code:
    SELECT Count(*), Sum(Cast(GrossWinAmount AS MONEY))
       FROM LGSLog 
       WHERE  CurrentDate >= '9/1/2004 8:00:00 AM'
          And CurrentDate <= '9/27/2004 7:59:59 AM'
    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    I don't think that would accomplish anything in this case since floating point and monitary values such as this are stored in the database, for the most part, as an SQL int type (multiplied by 100 and rounded in the program(s) before being written... don't ask why... it was being done this way before I started working on the system).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did you try it, or are you just guessing that it won't work?

    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    24
    Yep. I just tried changing the query as follows (I changed the date range to only include a week's worth of data... in the program, these are actually datetime variables)...
    :
    declare @Totals TABLE
    (
    GameCount int default 0,
    Win int default 0,
    Adj int default 0,
    Bet int default 0
    )
    declare @Count int
    INSERT INTO @Totals
    SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS MONEY)) As Win, Sum(CAST(l.AdjustedWinAmount AS MONEY)) As Adj, Sum(CAST(l.TotalBetAmount AS MONEY)) As Bet
    FROM LGSLog l
    WHERE (CurrentDate >= '9/20/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
    GROUP BY l.MasterID
    ORDER BY l.MasterID
    SELECT @Count=COUNT(*) FROM @Totals
    if (@Count = 0)
    Begin /* make sure something valid is returned if nothing found */
    INSERT INTO @Totals VALUES (0, 0, 0, 0)
    End
    SELECT * FROM @Totals
    :
    and got the following error...
    :
    There is insufficient result space to convert a money value to int.
    The statement has been terminated.
    :
    Which suggests to me that Sum is still wanting to RETURN an int size value, which is what I thought. The theory is that it is not the size of what is being passed into Sum(), but the fact that Sum() is trying to return a value that is too big because it wants to return a value the size of an int!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about the DDL for LGSLog

    And how many rows are in the result set?

    What are the MIN and MAX Values for those columns?

    What is the average wind-speed velocity of a sparrow?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Could it be that the result went outsideof the alloweable boundaries (922337203685477.5807 or -922337203685477.5808)?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Posts
    24
    I think I finally got it to work. It took a hybrid of your original suggestion. I cast the sum results to money values / 100 (floats gave me too imprecise a number). I also changed the result set data fields to money values. Then, in the program, I multiplied by 100 and rounded and then converted to long integer (the program already expcected the values as a fixed point type stored in a long... fixed 2 places left of the right most digit... it was easier this way than to make major mods throughout program). I just hope this does not introduce rounding errors, which was the main reason for storing as fixed point values converted to integers in the first place.

    This technique seemed to work getting data as far back as one month (no overflow/conversion errors).

    declare @Totals TABLE
    (
    GameCount int default 0,
    Win money default 0,
    Adj money default 0,
    Bet money default 0
    )
    declare @Count int
    INSERT INTO @Totals
    SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS money) / 100) As Win,
    Sum(CAST(l.AdjustedWinAmount AS money) / 100) As Adj, Sum(CAST(l.TotalBetAmount AS money) / 100) As Bet
    FROM LGSLog l
    WHERE (CurrentDate >= '9/01/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
    GROUP BY l.MasterID
    ORDER BY l.MasterID
    SELECT @Count=COUNT(*) FROM @Totals
    if (@Count = 0)
    Begin
    INSERT INTO @Totals VALUES (0, 0, 0, 0)
    End
    SELECT * FROM @Totals


    The grouping broke the results into anywhere from a couple of rows to over 25, depending on how far back I wanted to go. This also helped to reduce the size of the value being summed at the server. If the number got too big in the program, I could just use a larger integer size, such as an int32 or int64 in the program, which is not an option I had at the server.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Instead of casting to MONEY and doing the divide by 100 then multiplying the sum by 100, just cast the column to BIGINT which is an int64 equivalent.

    I'm still having trouble getting my head around the idea that you overflowed a MONEY... MONEY is big enough to express the US national debt in Argentine Pesos!

    -PatP

  10. #10
    Join Date
    Feb 2004
    Posts
    24
    I don't think it actually "OVERFLOWED" the money type. I think it had no problem working with money. I think what was happening is that when "Sum" RETURNED the sum value, SQL tried to convert the "money" value to an "int" type, overflowing the "int" type during the conversion. This is why I think that the fields in the result set had to go from the integerial type to a money. I think I tried converting the value being passed into Sum, and also tried storing the result into a bigint, but never both at the same time. It always resulted in a conversion to an integer at some point... going in or coming out of Sum.

    Apparently, if you pass an int type into Sum, it is going to return an int, which is what I was doing originally. If you pass in a money, it will sum internally as a money type and return a money type. But if the receiver variable is an int, it will downcast to an int, overflowing the resulting value. If you store the result into a money type, then it is going straight from a money to a money... no downcast necessary.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For what it is worth, MONEY actually stores as an 8 byte signed integer, with implicit scaling by 1e4 (basically four digits after the decimal place). If your application is dealing with ODBC data directly like a C++ program would, you'd see the result come back as a long int that needed to be divided by 10000 (or in your case only 100).

    -PatP

Posting Permissions

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