current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
    
and what values does column compliance hold? –  Mitch Wheat Aug 29 '11 at 14:46
    
it has values like 'N/A','100.0' or '99.1' ... i.e) it has both float and nvarchar –  goofyui Aug 29 '11 at 14:48
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
    
@Compliance has to accept both NVARCHAR and FLOAT. i.e) If there is no Compliance .. we need to set it as N/A –  goofyui Aug 29 '11 at 14:50
    
I'm not suggesting otherwise. –  Mitch Wheat Aug 29 '11 at 14:51
1  
@Chok, I think Mitch is right, you probably need to change your second case to WHEN compliance >= 100.0 (note the >= ) –  Paolo Falabella Aug 29 '11 at 14:53
    
@Mitch is right !! I changed my datatype to DECIMAL(4,1) –  goofyui Aug 29 '11 at 15:00
add comment
up vote 0 down vote

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.