Conversion failed

Question

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?

Answer 1

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.