Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Error converting data type varchar to numeric in CASE statement Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 9:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Chrissy321 (6/19/2013)
Column1 is varchar(100). Column2 is varchar(50).

WHERE CASE Column1 WHEN 'ABC' THEN 1 ELSE CONVERT(decimal(20,6),Column2)END) IS NOT NULL


Try putting single quotes around the '1' or cast it as VARCHAR-- the expression is trying to compare a string ('ABC') with a numeric (1) and that will generate that error. The same issue occurs in JOIN statements. It seems SQL won't do implicit conversions in these cases and just throws an error.

 
Post #1465784
Posted Thursday, June 20, 2013 10:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 653, Visits: 3,881
I think it's equivalent.


Oh I see, provide the easy and obvious answer. :)

I agree that this is equivalent.

This will work
SomeVal2 = 'ABC' or SomeVal is not null

This won't but I don't think I really need the CONVERT in the WHERE
SomeVal2 = 'ABC' or CONVERT(decimal(20,6), SomeVal) is not null

Definitely a data issue though. I rolled back my prod database and after new data load the existing code works. I can still duplicate the error in test.

thanks
Post #1465835
Posted Thursday, June 20, 2013 10:42 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 653, Visits: 3,881
Try putting single quotes around the '1' or cast it as VARCHAR-- the expression is trying to compare a string ('ABC') with a numeric (1) and that will generate that error. The same issue occurs in JOIN statements. It seems SQL won't do implicit conversions in these cases and just throws an error.

 


Same error. It seems implicit conversions can be a problem in CASE statement but something else is going on here.

It seems to be the conversion and then the NOT NULL evaluation

CONVERT(decimal(20,6), SomeVal) IS NOT NULL

I pulled my CASE statement right out of the SELECT and put it in the WHERE clause. As Phil pointed out the CASE and the CONVERT was not needed in the WHERE clause.

I'll plan on modifying my WHERE statements in case I get another dirty data load. I think we can put this to bed. Thanks everyone.





Post #1465842
Posted Thursday, June 20, 2013 3:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 1,060, Visits: 2,564
Chrissy321 (6/19/2013)
Sorry for the narrative but I can't seem to generate sample data to reproduce my problem...

I have a WHERE clause that worked reliably but is now generating the an error: Error converting data type varchar to numeric.

WHERE CASE Column1 WHEN 'ABC' THEN 1 ELSE CONVERT(decimal(20,6),Column2)END) IS NOT NULL

Column1 is varchar(100). Column2 is varchar(50).

If a comment out the WHERE clause and place the code in question in the select statement the query runs.

ISNUMERIC indicates column2 can be converted (when column1 <> 'ABC')

Somewhat mystified...



You have a value in Column2 that cannot be converted to decimal(20,6). ISNUMERIC() is not a reliable way of determining whether a varchar value can be converted to a numeric datatype. Try this:

SELECT ISNUMERIC('$452.00')
SELECT CONVERT(decimal(6,2), '$452.00')



Do you have any other conditions in your WHERE clause? If so, you may not have encountered this error before if those conditions allowed SQL Server to weed out the rows where Column2 cannot be converted to decimal(20,6) before it evaluated the CASE expression.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1465969
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse