CODEWITHSUNDEEP
sqlsql-servert-sql
Chad
Chad

Reputation: 24699

Conversion error due to mismatch data types

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

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

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.
  • The two ids should be of the same type! You can't even declare a proper foreign key relationship.
  • LEFT() is more convenient than SUBSTRING() in this case.
  • Use the table alias defined in the 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

Related Questions