Error converting data type varchar to numeric in CASE statement

  • 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...

  • Your sure your table does not have characters in column2 where column1 <> 'abc' ?

  • This works

    SELECT

    CONVERT(decimal(20,6),Column2)

    FROM ....

    WHERE column1 <> 'abc'

  • 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 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/

  • >>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

  • 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/

  • 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

  • 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/

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

     

  • 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

  • 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.

  • 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