Occasional conversion error using SUM function

Question

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

Answer 1

Such things happen (sometimes). The problem is (almost) always in an execution plan that SQL Server generates for your query. It may happen that SQL Server places converstion before you join/condition operator. That's why you get an error.

Forcing particular [execution] plan using hints or introducing additional conditions (as you did) is a key to resolving such issues.

Answer 2

try this:

sum(case
        when ISNULL(form_prsn_id, -1) = irpd_prsn_id then convert(dec(11,2), case
                                                                                 when ISNUMERIC(valu_value)=1 THEN valu_value
                                                                                 else 0
                                                                             end
                                                                 )* case
                                                                        when fmdt_deduction_flag = 'Y' then -1 
                                                                        else 1 
                                                                    end 
        else 0 
    end
   ) as client_sum

the difference is here:

OLD

case
    when valu_value = ''  then '0'
    else isnull(valu_value,'0')
end

NEW

case
    when ISNUMERIC(valu_value)=1 THEN valu_value
    else 0
end

Answer 3

Just a quick thought as you don't say how you are ensuring valu_value is numeric but can it contain a comma? e.g.

select isnumeric('23,00')

PASS!

select convert(dec(11,2),'23,00')

FAIL - Error converting data type varchar to numeric.

Answer 4

I have met this before, so the root cause may be the SQL Server 2005 process the conversion before do the join or where statement which is based on the Execution Plan.

my case is:

SELECT Convert(decimal(18,0), NVarcharColumn) FROM Table1

I Solve it by changing the statement to:

SELECT Convert(decimal(18,0), NVarcharColumn) FROM Table1
WHERE isnumeric(NvarcharColumn) = 1