locked
issue with ON varchar(50) = decimal (18,0) RRS feed

  • Question

  • I had several joins where I was joining a varchar(50) to a decimal (18,0).

     

    In the ‘where’ clause I always had

    where varchar(50) NOT LIKE '%[^0-9]%'



     

     

    All of a sudden all the joins started to fail with:

    Error converting data type varchar to numeric.

     

     

    The only thing I could find to fix the issue was adding on the ON clause:

     instead of


    ON varchar(50) = decimal (18,0).



    to do:

    ON try_convert(numeric,varchar(50)) = decimal (18,0).



     

     

    I haven’t found any offending row, why could this happened?

     

     my only suspect is that the decimal(18,0) column was before an int, and someone change the datatype to decimal(18,0), thoughts??

     
    • Edited by maca128 Monday, April 23, 2018 3:27 PM
    Monday, April 23, 2018 3:26 PM

All replies

  • I suppose , those are not actual columns names you are trying to give the column data types.

    Its not problem with int or decimal(18,0) columns types. Its with column with varchar(50) type.  The comparison works when varchar type column has values which can be converted to Numeric , other wise this error would be thrown. 

    To give more insight to your problem , you might want to post some sample data for both the columns


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, April 23, 2018 3:41 PM
  • I already gave you the reason in the earlier thread

    Because of some non numeric data coming in your varchar column the conversion is failing

    What you need to do is to first move numeric data alone to a temporary table or CTE and then use join to it. then it will work fine


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Naomi N Tuesday, April 24, 2018 12:08 PM
    Monday, April 23, 2018 3:43 PM
  • You don't have any control over the order of execution.  In some cases it may execute the WHERE clause first, in others it executes the ON clause first.

    Somewhere you have a varchar(50) which cannot be converted to decimal(18,0).  You should be able to find it with 

    SELECT *
    FROM TABLE
    WHERE try_convert(decimal(18,0),varcharcolumn) IS NULL

    PS. Please don't post the same question multiple times.


    • Edited by Tom Phillips Monday, April 23, 2018 5:36 PM
    • Proposed as answer by Naomi N Tuesday, April 24, 2018 12:08 PM
    Monday, April 23, 2018 5:34 PM
  • That’s the issue, ‘try_convert  … IS NULL’ it retrieves nothing.

     

    I have

    from table1
    join table2
    on t1.varchar = t2.decimal(18,0)
    where
    isnumeric (t1.varchar)=1


     

    and it failed with the conversion error, how is it possible having the isnumeric clause???

     

    If I added:

    from table1
    join table2
    on t1.varchar = t2.decimal(18,0)
    where
    isnumeric (t1.varchar)=1
    and try_convert(decimal(18,0),varcharcolumn) IS NULL


     
    Does not fail, but  retrieves nothing.

     

     

     

     

    Tuesday, April 24, 2018 8:39 AM
  • That’s the issue, ‘try_convert  … IS NULL’ it retrieves nothing.

     

    I have

    from table1
    join table2
    on t1.varchar = t2.decimal(18,0)
    where
    isnumeric (t1.varchar)=1


     

    and it failed with the conversion error, how is it possible having the isnumeric clause???

     

    If I added:

    from table1
    join table2
    on t1.varchar = t2.decimal(18,0)
    where
    isnumeric (t1.varchar)=1
    and try_convert(decimal(18,0),varcharcolumn) IS NULL


     
    Does not fail, but  retrieves nothing.

     

     

     

     

    As suggested already you cant force order in which optimizer creates the execution plan

    Can you try putting the details onto a temporary table after applying the necessary filters and then use it for your join operation with the other tables? This was the same I suggested in the other thread too but didnt get any response from you


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 24, 2018 8:51 AM
  • Ok, I agree with you, but what’s the best practice? I use sql server 2016, if I want to join a varchar to a decimal (18,0) how should I do it?

    Tuesday, April 24, 2018 10:36 AM
  • and it failed with the conversion error, how is it possible having the isnumeric clause???

     

    isnumeric is useless. It returns 1 if the value can be converted to any numeric data type. For instance isumeric('1E0') returns 1, but you cannot convert it to decimal. (It is a float value).

    If I added:

    from table1
    join table2
    on t1.varchar = t2.decimal(18,0)
    where
    isnumeric (t1.varchar)=1 and try_convert(decimal(18,0),varcharcolumn) IS NULL

    Does not fail, but  retrieves nothing.

    That query can never return any rows, since you filter out all values that can be converted to decimal are filtered out by the WHERE clause. Depending on the query plan it can fail if there are bad values.

    But you should have the try_convert in the JOIN. It's pointless to have it in the WHERE. Possibly you want to run

    SELECT * FROM table1 WHERE try_convert(decimal(18, 0), varcharcolumn) IS NULL
    to see the bad values.

    • Proposed as answer by Naomi N Tuesday, April 24, 2018 12:10 PM
    Tuesday, April 24, 2018 10:48 AM
  • Ok, I agree with you, but what’s the best practice? I use sql server 2016, if I want to join a varchar to a decimal (18,0) how should I do it?

    Did you try the method I suggested prevously? And see if it worked?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 24, 2018 10:52 AM
  • Best practice is not to join 2 different data types.  If you have a link between 2 tables, they should be the same data type.

    Your best option is to cast your decimal(18,0) value to a varchar(50), rather than the other way around.  That will always work.

    Also, this should be:

    and try_convert(decimal(18,0),varcharcolumn) IS NOT NULL

    If you want something to be returned.

    • Proposed as answer by Naomi N Tuesday, April 24, 2018 12:09 PM
    Tuesday, April 24, 2018 12:07 PM