Conversion failed

Bone_12 361 Reputation points
2021-09-10T08:23:06.433+00:00

Hi,

I have ran my code and have an error come up that I really don't know how to resolve.

Msg 245, Level 16, State 1, Line 40
Conversion failed when converting the nvarchar value '318.00' to data type int.

This is my code:

select distinct
substring(a.cust_no,5,6) as job_number,
a.mort_yearmonth as comp_date,
a.gross_profit as gp,
substring(b.supp_Code,3,7) as supp_id

from [TWX].[dbo].[CUST_JOBS] as a
left join [TWX].[dbo].[CUST_REF] as b
on substring(a.cust_no,5,6) = b.cust_no_only

Just to note - a.cust_no is usually set up as 1/000123456.123 where as b.cust_no_only is set up as 123456

I have tried to wrap a substring around a.cust_no so it can directly match to b.cust_no_only and I suspect that this is the problem here.

Any idea how to resolve this please?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points
    2021-09-10T08:58:49.42+00:00

    If b.cust_no_only ('123456') is expected to be a part of a.cust_no ('1/000123456.123'), then maybe try something like this:

    select . . .
    on substring(a.cust_no, charindex('/', a.cust_no) + 1, charindex('.', a.cust_no) - charindex('/', a.cust_no) - 1) = b.cust_no_only
    

    Or redesign the tables to avoid such string operations.

    0 comments No comments

0 additional answers

Sort by: Most helpful