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