SQL Server 2008 算术溢出错误将表达式转换为数据类型 int

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

提问by Federico Giust

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.

我在 SQL Server 中遇到了一个问题，我已经尝试调试了很长时间，但我不知道问题出在哪里。

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

例如，在我们有超过 4000 条记录的一个项目中，它不断给我这个错误

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.

我一直在谷歌搜索，但找不到解决这个问题的方法，我尝试过，因为你可以看到将所有数字字段转换为 big int 没有运气。

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.

问题是我需要结果中的 Dep_Amount。

Any help would be very much appreciated.

任何帮助将不胜感激。

采纳答案by Federico Giust

Found the problem!

发现问题了！

The problem was in the view.

问题出在视图上。

On this line where it was generating the Dep_Amount column

在生成 Dep_Amount 列的这一行

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

现在我将 else 转换为数字，错误消失了！！！:)

回答by Federico Giust

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

您不需要强制转换刚返回且未对其执行任何操作的列。我会按以下方式运行查询，并在当时取消注释一行以找出问题所在。很可能最后一行HAVING COUNT(是破坏性的

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