Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error converting data type varchar to numeric in CASE statement


Error converting data type varchar to numeric in CASE statement

Author
Message
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
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...
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
Your sure your table does not have characters in column2 where column1 <> 'abc' ?
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
This works

SELECT
CONVERT(decimal(20,6),Column2)
FROM ....
WHERE column1 <> 'abc'
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
Chrissy321 (6/19/2013)
This works

SELECT
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
>>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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 19457
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search