SQL: Conversion failed when converting the nvarchar value 'XXX' to data type int

1
votes

I am getting 'Conversion failed when converting the nvarchar value 'XXX' to data type int' error for no obvious reason and I don't know how to fix it. The view actually returns the correct data to stored procedure but it is giving me this error. I'm stuck. Anyone please give me some pointers?

SELECT          d.Code, a.Agency, a.Agency_job, a.OUCode, a.JobCode, d.OraUser, c.ProfileName
FROM            dbo.Agency AS a 
LEFT OUTER JOIN dbo.IdM AS d ON REPLACE(STR(a.OUCode, 3), ' ', '0') = d.OrganizationUnitCode AND a.JobCode = d.JobCode 
LEFT OUTER JOIN dbo.Profiles AS c ON c.JobCode = a.JobCode AND c.OUCode = a.OUCode
WHERE          (d.Code IS NOT NULL)

Column details - 

 d.Code is nvarchar(32),
 d.OraUser is nvarchar(100),
 d.OrganizationUnitCode is nvarchar(16),
 a.Agency is int,
 a.Agency_job is int,
 a.OUCode is int,
 a.JobCode is int,
 c.ProfileName is varchar(100),
 c.OUCode is int,
 c.JobCode is int.

Does anyone maybe see a potential problem?

3
sqlsql-servertsql
The WHERE clause condition makes the first LEFT JOIN return regular INNER JOIN result. - jarlh
Seems like a.OUCode contains some non-numeric values. - jarlh
You don't have conversion from string to integer in your query(only integer to string). Are you sure that you posted full query or give use correct types of columns? - Fabio
@Fabio I double checked the types, they are as I wrote them. This is a full query - view that I use in a stored procedure. - Link
@jarlh I checked the a.OUCode in a table and it doesn't contain any non-numeric value. - Link

3 Answers

2
votes

It is only normal for the error to happen in your query!

REPLACE(STR(a.OUCode, 3), ' ', '0') is of string type but d.OrganizationUnitCode on the right side of join predicate is an int. So it cannot implicitly do the conversion here, either convert the int field to nvarchar or the other way around. you can try something like this:

cast(REPLACE(STR(a.OUCode, 3), ' ', '0') as int) = d.OrganizationUnitCode

1
votes

If you have OUCode > 999 in your tables, the STR(a.OUCode, 3) will return *** which when trying to convert to int would give you the error 

Conversion failed when converting the varchar value '***' to data type int.

From books online : https://docs.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql?view=sql-server-2017

When the expression exceeds the specified length, the string returns ** for the specified length.

The other issue I can see is : What is the datatype of JobCode in your IdM Table ?. Try this and see if it returns any results : 

SELECT JobCode FROM dbo.IdM WHERE ISNUMERIC(JobCode) = 0
0
votes

Below is the answer:

SELECT          d.Code, a.Agency, a.Agency_job, a.OUCode, a.JobCode, d.OraUser, c.ProfileName
FROM            dbo.Agency AS a 
LEFT OUTER JOIN dbo.IdM AS d ON CAST(REPLACE(STR(a.OUCode, 3), ' ', '0') AS NVARCHAR(16)) = d.OrganizationUnitCode AND a.JobCode = d.JobCode 
LEFT OUTER JOIN dbo.Profiles AS c ON c.JobCode = a.JobCode AND c.OUCode = a.OUCode
WHERE          (d.Code IS NOT NULL)

Also choose a correct value while using STR function.