SQL Server 2008 Arithmetic overflow error converting expression to data type int
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!!! :)
Federico Giust
I've been doing software development for over 10 years now, most of the time as a full stack developer. But I've always loved technology, problem solving and learning new things. I'm an enthusiastic and self-motivated team player, always open for challenge, with experience working in fast paced environments including customer support. My ability to keep calm and optimistic as well as trouble-shooting in worst-case scenarios are my key strengths. Main skills: Software Development, Systems integration, API development, REST, Salesforce.com, Heroku, AngularJS, NodeJs, Selenium, PHP.
Updated on June 26, 2020Comments
-
Federico Giust almost 4 years
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.