Occasional conversion error using SUM function
- by user153777
My app uses sql2000 and a select statement it uses will sometimes fail. Once a week or so the select returns the error
'Error Converting data type varchar to numeric'
SQL:
sum(case when ISNULL(form_prsn_id, -1) = irpd_prsn_id
then convert(dec(11,2), case when valu_value = ''
then '0'
else isnull
(valu_value,'0')
end)* case when
fmdt_deduction_flag = 'Y'
then -1
else 1
end
else 0
end) as client_sum
The valu_value field is a varchar and stores some numeric and some varchar. But including my join and where clause filter
it will always select numeric or empty string.
When it is failing I can remove the SUM, see the data and know that its numeric.
So why would the SUM function sometimes (say 5% of time) fail on data that is numeric.
I wonder if SQL somehow "looks ahead" to ensure it could convert to decimal on more than just the rows returned without the sum.
Note I have discovered a fix where I include ( where isNumeric(valu_value) = 1 )
Thanks
