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 Wednesday, June 19, 2013 10:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 644, Visits: 3,702
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...

Post #1465274
Posted Wednesday, June 19, 2013 11:29 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020
Your sure your table does not have characters in column2 where column1 <> 'abc' ?
Post #1465304
Posted Wednesday, June 19, 2013 11:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 644, Visits: 3,702
This works

SELECT
CONVERT(decimal(20,6),Column2)
FROM ....
WHERE column1 <> 'abc'
Post #1465306
Posted Wednesday, June 19, 2013 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 12,016, Visits: 11,046
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)
Post #1465320
Posted Wednesday, June 19, 2013 2:28 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 644, Visits: 3,702
>>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
Post #1465382
Posted Wednesday, June 19, 2013 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 12,016, Visits: 11,046
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)
Post #1465390
Posted Wednesday, June 19, 2013 3:23 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 644, Visits: 3,702
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
Post #1465401
Posted Wednesday, June 19, 2013 3:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 12,016, Visits: 11,046
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)
Post #1465411
Posted Wednesday, June 19, 2013 3:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 644, Visits: 3,702
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
Post #1465413
Posted Thursday, June 20, 2013 12:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:23 PM
Points: 4,832, Visits: 11,197
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1465468
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse