T-SQL Error converting data type on join, on non-joining field-CodePudding

A query is written to join data from two tables and group it. If the two lines defining the join are commented out, the query returns correctly. Here is the query (with the join commented out):

SELECT tblTurbineLocations.TurbineLayoutProjectID as ProjectID
      ,TurbineLayoutNumber
      ,Count(HubHeight) as NumTurbines
      ,tblTurbineLocations.WTCode
FROM [TurbineLayout].[dbo].[tblTurbineLocations]
--LEFT OUTER JOIN [TurbineModel].[dbo].[tblTurbineModels]
--ON str(tblTurbineLocations.WTCode) = str(tblTurbineModels.WTCode) --Need to force string conversion to avoid data type conflict.
WHERE tblTurbineLocations.TurbineLayoutProjectID = 2255
AND tblTurbineLocations.TurbineLayoutNumber IN (406, 407)
GROUP BY tblTurbineLocations.TurbineLayoutProjectID ,tblTurbineLocations.TurbineLayoutNumber ,tblTurbineLocations.WTCode

Removing the two commented join lines then attempts a join on the WTCode field. This returns the following error:

Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to float.

The error points to line 2, rather than the line containing the join. The error raises that nvarchar cannot be converted to float. However the column in line 2, tblTurbineLocations.TurbineLayoutProjectID, is not an nvarchar; it is an int:

Reviewing the other columns in the query, none are of type nvarchar save for the joining column, WTCode (nvarchar(11) in one table, nvarchar(5) in the other). Both are cast as strings to avoid a different error (that is resolved by casting as str):

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

WTCode is not being cast as a float in the query.

What is the error in my code or my approach?

CodePudding user response：

As indicated by Larnu in the comments:

The issue is that the "str" function is expecting a float, thus is returning the error when it is fed an nvarchar. To solve the collation conflict, COLLATE can be used after the join to confirm what collation should be used for the nvarchar fields. Thus the following works:

SELECT tblTurbineLocations.TurbineLayoutProjectID as ProjectID
      ,TurbineLayoutNumber
      ,Count(HubHeight) as NumTurbines
      ,tblTurbineLocations.WTCode
FROM [TurbineLayout].[dbo].[tblTurbineLocations]
LEFT OUTER JOIN [TurbineModel].[dbo].[tblTurbineModels]
ON tblTurbineLocations.WTCode = tblTurbineModels.WTCode
COLLATE Latin1_General_CS_AS_KS_WS
WHERE tblTurbineLocations.TurbineLayoutProjectID = 2255
AND tblTurbineLocations.TurbineLayoutNumber IN (406, 407)
GROUP BY tblTurbineLocations.TurbineLayoutProjectID ,tblTurbineLocations.TurbineLayoutNumber ,tblTurbineLocations.WTCode