SQL Query Error - Error converting data type varchar to numeric.

[MichaelRSnow]

I appreciate this is not an excel/VBA type query but can't find an obvious forum to post it? hopefully someone can help?

I have this SQL query which works when applied to non-numeric data (text) but I get the below error when using the code on numeric data

error - Error converting data type varchar to numeric?

 , CASE ISNULL(ProductRate, 'NULLVALUE') 
WHEN 'NULLVALUE' THEN ''
ELSE ProductRate
END AS ProductRate

On text fields it converts NULL to Blank, can anyone help?

Thank you

 

[smozgur]

Converting should help, because you need "empty" string in the case of NULL of a numeric field value as return. That's why SQL get confused.

Additional note - I don't think you need CASE WHEN. ISNULL would do it alone.

, ISNULL(CONVERT(char, ProductRate), '') AS ProductRate

 

[MichaelRSnow]

Converting should help, because you need "empty" string in the case of NULL of a numeric field value as return. That's why SQL get confused.

Additional note - I don't think you need CASE WHEN. ISNULL would do it alone.

, ISNULL(CONVERT(char, ProductRate), '') AS ProductRate

Perfect, Thank you so much