Geek asks and answers

SQL arithmetic excess error while converting expression to nvarchar data type

Error: Arithmetic overflow error converting expression to nvarchar data type.

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

Since I am dividing into Actual, I want to make sure that Actual is not equal to zero. But when I add the instruction to WHERE CLAUSE, I can not get rid of the error.

+3
source share
1 answer

Maybe try 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
+1
source

More articles:


All Articles