SQL: Arithmetic overflow error converting numeric data type to numeric

1
votes

I am trying to create the following view:

CREATE VIEW [dbo].[_TEST]
AS
    SELECT
        a.[PS],
        b.[A0001],
        CAST(a.[Area] AS DECIMAL(15, 10)) AS A0002,
        CAST((b.[0001] / a.[Area]) AS DECIMAL(15, 10)) AS A0003
    FROM
        dbo.Areas AS a
    INNER JOIN
        (SELECT 
             LEFT(PSOA, 6) AS PS, SUM([0001]) as A0001
         FROM 
             dbo.Counts
         GROUP BY 
             LEFT(PSOA, 6) AS b ON a.PS = b.PS

The view creates fine but I get the error below when using it:

Arithmetic overflow error converting numeric data type to numeric

Now, from my understanding, that means the values are too big to fit into a Decimal(15,10). However, manually calculating them, the biggest value I get for A0003 is 2151.93382, which is fine for a Decimal(15,10). I even tried Decimal(20,10) just to be sure and that returns the same error. The figures are definitely not too big, so what's going wrong?

2
sqlsql-server
However, manually calculating them ... don't manually calculate anything. Do a SELECT MAX(value) from your table and see what the largest value actually is. - Tim Biegeleisen

2 Answers

0
votes

I found that the max Area value was actually a Decimal(16,10) which explains this. Not sure how I missed that!

0
votes

This is not giving the error and that error might be due to some other reason. Check with your other data which can be bigger than this.

Select Cast(2151.93382 as Decimal(15,10))

You can check the demo here.

How many rows are there in the table I think SUM([0001]) can produce a big number in resulting arithmetic overflow exception.