CODEWITHSUNDEEP
sqlsql-server
Natan
Natan

Reputation: 139

Getting 'Error converting data type varchar to numeric.' even after converting

I have a simple sql query

select top 100 
    pickticket_number,
    date_allocated,
    l2.action
from  
    [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets P (nolock)
left join
    [A1Warehouse].[dbo].[RF_LOG2] (NOLOCK) l2 ON l2.PACKSLIP = p.PickTicket_Number

It seems to be a simple converting/casting issue but no matter what conversion or casting I try, I get the same error.

I've tried all of the following

LEFT JOIN
    [A1Warehouse].[dbo].[RF_LOG2] (NOLOCK) l2 ON convert(numeric,l2.PACKSLIP) = p.PickTicket_Number

LEFT JOIN
    [A1Warehouse].[dbo].[RF_LOG2] (NOLOCK) l2 ON convert(numeric,l2.PACKSLIP) = convert(numeric, p.PickTicket_Number)

LEFT JOIN
    [A1Warehouse].[dbo].[RF_LOG2] (NOLOCK) l2 ON cast(l2.PACKSLIP as decimal(22,8)) = cast(p.pickticket_number as decimal(22,8))

However, none of that or any combination of casting or converting seems to work. Does anybody know what I'm doing wrong?

Upvotes: 0

Views: 52

Answers (2)

lookslikeanevo
lookslikeanevo

Reputation: 575

it looks like you have data that cannot be converted into a number/decimal

cast/convert as varchar

select top 100 
    pickticket_number,
    date_allocated,
    l2.action
from  
    [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets P (nolock)
left join
    [A1Warehouse].[dbo].[RF_LOG2] (NOLOCK) l2 ON cast(l2.PACKSLIP as varchar) = cast(p.PickTicket_Number as varchar)

then troubleshoot each table to see what non numeric characters are in that field

by using isnumeric

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You clearly have invalid data. You can find it by doing something like this:

select packslip
from [A1Warehouse].[dbo].[RF_LOG2]
where try_cast(l2.PACKSLIP as decimal(22,8)) is null and l2.PACKSLIP is not null;

As for your query, you can avoid the error by using try_cast(). However, you should see what the actual problems in your data are.

And, your foreign key relationships should have matching types and be properly declared.

Upvotes: 1

Related Questions