Reputation: 24699
When I execute this
UPDATE #Export
SET LANID = SubString(DBF.LANID,1,10),New_LANID = DBF.LANID
FROM #Export SE
Left JOIN tbl_PERSON_BLUE_ALL_VW_SOAtoOracle DBF
ON
Convert(Int,DBF.persn_Id)=SE.emp_id
I get this error
Msg 245, Level 16, State 1, Line 159
Conversion failed when converting the varchar value '000G76007' to data type int.
Modifying the JOIN seems to fix it.
UPDATE #Export
SET LANID = SubString(DBF.LANID,1,10),New_LANID = DBF.LANID
FROM #Export SE
Left JOIN tbl_PERSON_BLUE_ALL_VW_SOAtoOracle DBF
ON
TRY_CAST(DBF.persn_Id as int) IS NOT NULL AND
Convert(Int,DBF.persn_Id)=SE.emp_id
Is there still a possibility that this may fail? Is there a better fix?
Upvotes: 0
Views: 267
Reputation: 1270993
Use try_convert()
!
UPDATE SE
SET LANID = LEFT(DBF.LANID, 10),
New_LANID = DBF.LANID
FROM #Export SE LEFT JOIN
tbl_PERSON_BLUE_ALL_VW_SOAtoOracle DBF
ON TRY_CONVERT(INT, DBF.persn_Id) = SE.emp_id ;
Notes:
TRY_CONVERT()
will eliminate the conversion errors. By converting to an int, you also eliminate problems with leading zeros.LEFT()
is more convenient than SUBSTRING()
in this case.FROM
clause. I actually consider it a bug in SQL Server that you can directly reference the table even though it has a table alias.Upvotes: 1