SQL Server 2008 Arithmetic overflow error converting expression to data type int

Question

I'm having an issue in SQL Server I've been trying to debug for quite some time now and I can't figure out where the problem is.

If I run this query…

SELECT 
    CAST(Hotel_Id AS bigint) AS Hotel_Id, 
    Hotel_Name, 
    CAST(Room_Category AS bigint) AS Room_Category, 
    Room_Category_Name, 
    CAST(Room_Type AS bigint) AS Room_Type, 
    Room_Type_Name, 
    Info_URL_Title, 
    Info_URL , 
    MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) AS Price_Excl_VAT, 
    CASE 
        WHEN MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) = 0 
        THEN 0 ELSE MAX(CAST(ISNULL(Price, 0) AS bigint)) - MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS bigint)) 
    END AS VAT,
    MAX(CAST(ISNULL(Price, 0) AS bigint)) AS Price,
    MAX(CAST(ISNULL(Dep_Amount, 0) AS [bigint])) AS Dep_Amount  
FROM 
    uvw_HotelAllotmentToBook 
WHERE Client_Id = 'CLIENT' AND Project_Id = 'PROJECT' 
    AND Allotment_Date >= '2014-05-11' AND Allotment_Date < '2014-05-14' 
GROUP BY Hotel_Id, Hotel_Name, Room_Category, Room_Category_Name, Room_Type, Room_Type_Name, Info_URL_Title, Info_URL, Dep_Amount
HAVING COUNT(Allotment_Date) >= 3

In for example one project that we have a bit over 4000 records it keeps giving me this error

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

I've been googling around but can't find a way to fix this, I tried as you can see casting all number fields to big int with no luck.

I need to find the MAX of the prices because the price per night can change.

UPDATE

SELECT Hotel_Id
   ,Hotel_Name
   ,Room_Category AS Room_Category
   ,Room_Category_Name
   ,Room_Type AS Room_Type
   ,Room_Type_Name
   ,Info_URL_Title
   ,Info_URL
   ,COUNT(Allotment_Date)
   ,MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) AS Price_Excl_VAT
   ,CASE WHEN MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) = 0 THEN 0
         ELSE MAX(CAST(ISNULL(Price, 0) AS BIGINT)) - MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT))
    END AS VAT
   ,MAX(CAST(ISNULL(Price, 0) AS BIGINT)) AS Price
   ,MAX(CAST(ISNULL(Dep_Amount, 0) AS BIGINT)) AS Dep_Amount
FROM uvw_HotelAllotmentToBook
WHERE Client_Id = 'PCWHK'
    AND Project_Id = 'INTA14'
    AND Allotment_Date >= '2014-05-11'
    AND Allotment_Date < '2014-05-14'
GROUP BY Hotel_Id
   ,Hotel_Name
   ,Room_Category
   ,Room_Category_Name
   ,Room_Type
   ,Room_Type_Name
   ,Info_URL_Title
   ,Info_URL
   --,Dep_Amount
HAVING COUNT(Allotment_Date) >= 3

I keep getting the same the overflow error but the moment I comment out this line

   ,MAX(CAST(ISNULL(Dep_Amount, 0) AS BIGINT)) AS Dep_Amount

The error is gone.

The problem is that I need the Dep_Amount in the result.

Any help would be very much appreciated.

How about some real data (a row from your database that you know fails)? The table schema would be nice, too. Arithmetic overflow error converting expression to data type int. sounds like maybe you have a floating point number that exceeds the allowable big int? — Phil Perry
– Phil Perry, Commented Jan 21, 2014 at 19:14
It only fails when it's a "lot" of records. With a small project it doesn't give any errors. — Federico Giust
– Federico Giust, Commented Jan 21, 2014 at 19:17
Remove columns one at a time until you find the troublemaker. Then inspect the values in that column for the range of rows you are handling. For handling pricing you may need to use a datatype with a larger range, e.g. NUMERIC. You may want to use COALESCE( MAX( Column ), 0 ) rather than casting. — HABO
– HABO, Commented Jan 21, 2014 at 19:21
Price is an integer? Does it work without that CASE statement? — Hart CO
– Hart CO, Commented Jan 21, 2014 at 19:24

Federico Giust · Accepted Answer · 2014-01-21 19:40:33Z

4

Found the problem!

The problem was in the view.

On this line where it was generating the Dep_Amount column

CASE 
WHEN COALESCE(hap.Charge_Dep_Amount, 0) = 0 
THEN COALESCE(hap.Dep_Amount, 0) 
ELSE (COALESCE(CAST(hap.Dep_Amount AS numeric), 0) * COALESCE(CAST(hap.Price AS numeric), 0)) / 10000 
END AS Dep_Amount,

Now that I'm casting the else as numeric the error is gone!!! :)

answered Jan 21, 2014 at 19:40

Federico Giust

1,7934 gold badges22 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user275683 · Accepted Answer · 2014-01-21 19:17:09Z

0

You do not need to cast columns that are just being returned without any operations performed on them. I would run query in the following way and just uncomment one line at the time to figure out where the problem is. It is likely that very last line HAVING COUNT( is what is breaking

SELECT Hotel_IdAS Hotel_Id
   ,Hotel_Name
   ,Room_Category AS Room_Category
   ,Room_Category_Name
   ,Room_Type AS Room_Type
   ,Room_Type_Name
   ,Info_URL_Title
   ,Info_URL
   ,COUNT(Allotment_Date)
   --,MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) AS Price_Excl_VAT
   --,CASE WHEN MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT)) = 0 THEN 0
   --      ELSE MAX(CAST(ISNULL(Price, 0) AS BIGINT)) - MAX(CAST(ISNULL(Price_Excl_VAT, 0) AS BIGINT))
   -- END AS VAT
   --,MAX(CAST(ISNULL(Price, 0) AS BIGINT)) AS Price
   --,MAX(CAST(ISNULL(Dep_Amount, 0) AS [bigint])) AS Dep_Amount
FROM uvw_HotelAllotmentToBook
WHERE Client_Id = 'CLIENT'
    AND Project_Id = 'PROJECT'
    AND Allotment_Date >= '2014-05-11'
    AND Allotment_Date < '2014-05-14'
GROUP BY Hotel_Id
   ,Hotel_Name
   ,Room_Category
   ,Room_Category_Name
   ,Room_Type
   ,Room_Type_Name
   ,Info_URL_Title
   ,Info_URL
   ,Dep_Amount
--HAVING COUNT(Allotment_Date) >= 3

answered Jan 21, 2014 at 19:17

user275683

3 Comments

Hart CO Over a year ago

OP indicates 4000 records in the table, so COUNT() shouldn't be causing an overflow.

Federico Giust Over a year ago

I pasted the query exactly as you posted here and still gives me the same error. If I only comment out this line ,MAX(CAST(ISNULL(Dep_Amount, 0) AS [bigint])) AS Dep_Amount it works, the moment I remove the comment from that line I start getting the same error.

user275683 Over a year ago

so it works with that line but does not work without it?

Collectives™ on Stack Overflow

SQL Server 2008 Arithmetic overflow error converting expression to data type int

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related