June 19, 2013 at 10:07 am
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...
June 19, 2013 at 11:29 am
Your sure your table does not have characters in column2 where column1 <> 'abc' ?
June 19, 2013 at 11:33 am
This works
SELECT
CONVERT(decimal(20,6),Column2)
FROM ....
WHERE column1 <> 'abc'
June 19, 2013 at 12:14 pm
Chrissy321 (6/19/2013)
This worksSELECT
CONVERT(decimal(20,6),Column2)
FROM ....
WHERE column1 <> 'abc'
What about this?
This will likely throw the same error
select *
from
where column1 = 1
How about:
select *
from
where IsNumeric(Column1) = 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2013 at 2:28 pm
>>where column1 = 1
This throws the error Conversion failed when converting the varchar value '-4.67625' to data type int.
>>where IsNumeric(Column1) = 0
This will exclude one row. This is the row I intend to catch in my CASE statement where Column1 is null.
where IsNumeric(Column1) = 0 shows one row, again the statement I intend to includ in my case statement.
Thanks
June 19, 2013 at 2:48 pm
Well then you have something else going on. We are at an extreme disadvantage since we don't know your table structures.
The following code works just fine.
if OBJECT_ID('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
SomeVal varchar(10)
)
insert #MyData
select '10' union all
select '125' union all
select '-4.67625' union all
select 'abc'
select * , case SomeVal when '-4.67625' then 2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal) end
from #MyData
From what you have posted that is as close as I can get to your issue here. I can't imagine why you are getting the convert to int error since you said you are converting to decimal. If you can post the table structure and the entire query you running it will help understand what is going on.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2013 at 3:23 pm
I appreciate you spending any time on this at all since it seems like a data issue,
Below is more representative of the situation. Removing the WHERE statement and the query will run. Its when I include the CASE statement in the query and check if it is not null where the error is generated
if OBJECT_ID('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
SomeVal varchar(10),
SomeVal2 varchar(10)
)
insert #MyData
select '10', 'DEF' union all
select '125', 'HIJ' union all
select '-4.67625', 'LMN' union all
select NULL, 'ABC'
select
*,
case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal)END
from #MyData
WHERE
case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal) end IS NOT NULL
June 19, 2013 at 3:37 pm
Chrissy321 (6/19/2013)
I appreciate you spending any time on this at all since it seems like a data issue,Below is more representative of the situation. Removing the WHERE statement and the query will run. Its when I include the CASE statement in the query and check if it is not null where the error is generated
What happens if you drop the case expression in the where clause? It really is not necessary because you have said that if the value is not 'ABC' then use the decimal conversion. This is the same thing as just checking for NULL.
select
*,
case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal)END
from #MyData
WHERE SomeVal2 IS NOT NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2013 at 3:41 pm
It works if I drop it in the case statement but I think I need it. Additional sample data below.
I want to exclude XYZ and include ABC replacing its NULL with 1
if OBJECT_ID('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
SomeVal varchar(10),
SomeVal2 varchar(10)
)
insert #MyData
select '10', 'DEF' union all
select '125', 'HIJ' union all
select '-4.67625', 'LMN' union all
select NULL, 'ABC' union all
select NULL, 'XYZ'
select
*,
case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal)END
from #MyData
WHERE
case SomeVal2 w
June 20, 2013 at 12:26 am
I've just skimmed this thread so apologies if this is not what you're looking for. But why not adjust the WHERE clause as follows:
where SomeVal2 = 'ABC' or SomeVal is not null
I think it's equivalent.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 20, 2013 at 9:31 am
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.
June 20, 2013 at 10:34 am
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
June 20, 2013 at 10:42 am
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.
June 20, 2013 at 3:32 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply