将表达式转换为数据类型 nvarchar 的 SQL 算术溢出错误

Question

错误:将表达式转换为数据类型 nvarchar 时出现算术溢出错误。

UPDATE [dbo].ForecastAccuracyKeyAccounts
SET ThreeMonthPercent = ((Actual - ThreeMonthForecast) / Actual) * 100, SixMonthPercent = ((Actual - SixMonthForecast) / Actual) * 100, 
NineMonthPercent = ((Actual - NineMonthForecast) / Actual) * 100
WHERE Actual != 0

因为我要除以 Actual，所以我想确保 Actual 不等于零。但是当我在 WHERE CLAUSE 中添加语句时，我无法摆脱错误。

Answer 1

也许试试 ISNULL

UPDATE [dbo].ForecastAccuracyKeyAccounts

    SET ThreeMonthPercent = ((Actual - ISNULL(ThreeMonthForecast,0)) / Actual) * 100,

    SixMonthPercent = ((Actual - ISNULL(SixMonthForecast,0)) / Actual) * 100, 

    NineMonthPercent = ((Actual - ISNULL(NineMonthForecast,0)) / Actual) * 100

    WHERE Actual != 0