April 3, 2014 at 10:40 am
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine.
CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red
ELSE '3' --White
END
END
else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
CASE WHEN LEFT(ROUND(NRESULT,2),5) < 0.00 and LEFT(ROUND(NRESULT,2),5) > -0.50 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),5) < -0.49 THEN '0' --Red
ELSE '3' --White
END
END
else '3' --White
end
end
A similar ELSE CASE statement that is working just fine. I even copied/pasted the below text into the above statement, and changed the values, but it still errs out.
else case when LEFT(NRESULT,1) = '-' then
CASE WHEN LEFT(ROUND(NRESULT,2),5) < 0.00 and LEFT(ROUND(NRESULT,2),5) > -0.05 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),5) < -0.04 and LEFT(ROUND(NRESULT,2),5) > -0.10 THEN '1' --Yellow
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),5) < -0.09 THEN '0' --Red
ELSE '3' --White
END
END
END
else '3' --White
end
end
Does anyone have any troubleshooting ideas, or possibly notice something within the script that does not look right? I checked the NRESULT field, and there are not any NULL values in there, either.
April 3, 2014 at 10:48 am
I got it resolved. I forgot to remove the * from the end of the query that I was building, so I am not sure what it was that was not "playing nice" with my CASE statement. I usually add the * to see the specific values I am working with before starting to narrow the fields down, or modify them, and then remove it when I have the exact script that I need.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply