Wednesday, October 01, 2014
   
Text Size
Login

T-SQL Conversion failed when converting the varchar to data type int

What?
A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.

How?
So where does the 'B110' string come from, well from one of our tables which looks similar to the following:

  1.  Table: DMExtractEmployeeTable 
  2.  ------------------------------------------- 
  3.  ID     Name         EmpType       CodeType 
  4.  1      Joe          Manager       0011 
  5.  2      John         Tape-Monkey   8475 
  6.  3      Fred         Director      7749 
  7.  4      Bill         Manager       0011 
  8.  5      Another      Tape-Monkey   8475 
  9.  6      Joe          Tape-Monkey   8475 
  10.   
  11.   
  12.  Table: DMExtractReferenceTable 
  13.  ------------------------------ 
  14.  ID     MapIn        MapOut 
  15.  1      0011         71 
  16.  2      8475         84 
  17.  3      7749         63 
  18.  4      B110         92 
  19.   
  20.  -- so we have 1 director, 2 managers, and 3 tape-monkeys 

Now suppose we had a convoluted query which needs to join the two tables so that we get the "MapOut" value:

  1.  SELECT 
  2.          r1.MapIn 
  3.          ( 
  4.                  SELECT 
  5.                          (COUNT(e2.EmpType)-1) AS MyCount 
  6.                  FROM 
  7.                          DMExtractEmployeeTable e2 
  8.                  LEFT JOIN 
  9.                          DMExtractReferenceTable r2 
  10.                          ON e2.CodeType=r2.MapIn            --  THIS IS THE LINE CAUSING THE PROBLEM!!! 
  11.                  WHERE 
  12.                          e2.ID=e1.ID 
  13.                  GROUP BY 
  14.                          e2.ID,r2.MapIn 
  15.          ) AS MatchingRecordsCount 
  16.  FROM 
  17.          DMExtractEmployeeTable1 e1 
  18.  LEFT JOIN 
  19.          DMExtractReferenceTable r1 
  20.          ON e1.CodeType=r1.MapIn 
So why do I get the error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'B110' to data type int.
I'm not using any of the non-numeric values to map to... Well the system thinks I might so it fails. I realised then the equals sign ("=") could be changed to a "LIKE" which will mean both values are expected to be a string.

So with this in mind:

  1.  SELECT 
  2.          r1.MapIn 
  3.          ( 
  4.                  SELECT 
  5.                          (COUNT(e2.EmpType)-1) AS MyCount 
  6.                  FROM 
  7.                          DMExtractEmployeeTable e2 
  8.                  LEFT JOIN 
  9.                          DMExtractReferenceTable r2 
  10.                          ON e2.CodeType LIKE r2.MapIn               --  FIXED!!! 
  11.                  WHERE 
  12.                          e2.ID=e1.ID 
  13.                  GROUP BY 
  14.                          e2.ID,r2.MapIn 
  15.          ) AS MatchingRecordsCount 
  16.  FROM 
  17.          DMExtractEmployeeTable1 e1 
  18.  LEFT JOIN 
  19.          DMExtractReferenceTable r1 
  20.          ON e1.CodeType=r1.MapIn 

So simply change the "=" to a "LIKE".


Scenario #2
So when the above has been addressed or we've tried casting as varchars all over the place, check the value that gets outputted is also a literal string. So here's some ok code but it will fail if my reference data has a non-numeric value in it:

  1.  -- Initial Query 
  2.  SELECT 
  3.       CASE 
  4.            WHEN emp.ContributionValue=1     THEN 5010 
  5.            WHEN emp.ContributionValue=2     THEN 5011 
  6.            WHEN emp.ContributionValue=3     THEN 5012 
  7.            ELSE 9999 
  8.       END 
  9.  FROM [dbo].[DMExtractEmployeeTable] emp 
  10.  INNER JOIN [dbo].[DMExtractReferenceTable] ref 
  11.       ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue 
  12.       AND ref.[RefSheet] = 'myPayrollCategory' 
  13.       AND ref.[RefSystem] = 'myPayrollSystem' 
  14.   
  15.  -- Yields "Conversion failed..." 

Working version:

  1.  -- Corrected query (output values enclosed in apostrophes) 
  2.  SELECT 
  3.       CASE 
  4.            WHEN emp.ContributionValue=1     THEN '5010' 
  5.            WHEN emp.ContributionValue=2     THEN '5011' 
  6.            WHEN emp.ContributionValue=3     THEN '5012' 
  7.            ELSE '9999' 
  8.       END 
  9.  FROM [dbo].[DMExtractEmployeeTable] emp 
  10.  INNER JOIN [dbo].[DMExtractReferenceTable] ref 
  11.       ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue 
  12.       AND ref.[RefSheet] = 'myPayrollCategory' 
  13.       AND ref.[RefSystem] = 'myPayrollSystem' 


Yes well obvious to some but then you google the above and you get half a million results. That's a lot of reading so here's the simple fix for my issue:

Returning an error:

  1.  LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails 
  2.   
  3.  // yields Conversion failed when converting the varchar value ', ' to data type int 
My issue was silly T-SQL concatenation which requires plus signs and thinks it's super clever when it tries to add a number and a string together; then remembers it's a Microsoft by-product and realises it can't.

Solved by checking the datatypes for each field in the concatenation string:

  1.  LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4), rm.[Capacity]) + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails 
  2.   
  3.  // success! added CONVERT(VARCHAR(4) to the room capacity which was of datatype int. 


Comments   

g
# g Mon, 2nd September 2013
What's the column types of the tables?
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts