Arithmetic overflow error converting numeric to data type numeric

Question

I am facing an error on my SQL script:

Arithmetic overflow error converting numeric to data type numeric

select x.MemberName,x.DOB,x.FilePath,x.Medication,x.NDC,x.Directions,x.Name,x.Strength,x.GenericName,x.QtyOrdered,x.DaysSupply,x.DateFilled, 
CASE
    WHEN x.test = 0  THEN 'N/A'
    WHEN compliance > 100.0   THEN '100.0'
    ELSE CONVERT(VARCHAR(5), CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1)))
END as [Compliance]

I am facing the error on just above syntax line.

Answer 1

Here's your problem:

declare @compliance decimal(10,5)

set @compliance = 100.0  --  <----------------

select CAST(FLOOR(@compliance *10)/10.0 AS DECIMAL(3,1))

Throws "Arithmetic overflow error converting numeric to data type numeric" error. Changing to DECIMAL(4,1) works, or as @paola suggests, change your condition to >= 100.0

decimal(p,s): 

p (precision) is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) is the number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point.

In your case decimal(3, 1) means a total of 3 digits with 1 digit to the right of the decimal point,

99.9

whereas decimal(4,1) provides a total of 4 digits with 1 digit to the right of the decimal point,

999.9

Answer 2

This questions has already been answered, but the why is important.

Numeric defines the TOTAL number of digits, and then the number after the decimal.

So DECIMAL(4,1) shows 123.4 DECIMAL(4,3) shows 1.234

In both cases you have a total of 4 digits. In one case you have 1 after the decimal, leaving 3 in front of the decimal. And vice versa.