What can I do to debug this SQL Error: Conversion failed when converting nvarchar value 'DIST00-CodePudding

I am not certain how to correct the SQL below to stop receiving the following error, Conversion failed when converting Nvarchar value 'Dist00' to int. Here is the Sql:

SELECT g.[Period], g.BuyingGroup, g.NonJDEGroup, g.GroupCode, g.Percentage,
    c.DistributorId as CustomerNumber, 
    ci.CustomerName,
    ri.ShipTo,
    ci.ShipToTiedTo, 
    b.BidId,
    b.Account,
    ri.InvoiceNumber, 
    ri.InvoiceDate, 
    ri.DateModified, 
    i.ItemId,
    CASE WHEN ri.NetBilled=0 THEN round(ri.SalePrice * ri.Quantity, 2) ELSE 0 END AS SaleTotal, 
    CASE WHEN ri.NetBilled=1 THEN round(ri.BidPrice * ri.Quantity, 2) ELSE 0 END AS BidTotal, 
    CASE WHEN ri.SalePrice <= ri.BidPrice THEN 0 ELSE (ri.SalePrice - ri.BidPrice) * ri.Quantity END AS RebateTotal,
    ri.NetBilled,
    ri.Quantity,
    b.Bid_TypeId,
    b.Bid_ClassId,
    CAST(CASE WHEN (ri.DateModified >= @SalesStartDate and ri.DateModified <= @SalesEndDate) THEN 1 ELSE 0 END AS bit) AS isCurrent,
    CAST(CASE WHEN (ri.DateModified >= @LateStartDate and ri.DateModified <= @LateEndDate) THEN 1 ELSE 0 END AS bit) AS isLate 
FROM [BQM_Dev].dbo.Claims c 
    INNER JOIN [BQM_Dev].dbo.Claim_Items i ON c.ClaimId=i.ClaimId
    INNER JOIN [BQM_Dev].dbo.Rebate_Items ri ON ri.Claim_ItemId = i.Claim_ItemId
    INNER JOIN [BQM_Dev].dbo.Bids b on b.BidId = i.BidId 
    INNER JOIN [BQM_Dev].dbo.Bid_Classes bc ON bc.Bid_ClassId=b.Bid_ClassId
    LEFT OUTER JOIN #CustomerInformation ci ON ci.CustomerNumber=c.DistributorId AND ci.RecordType='C'
    LEFT OUTER JOIN #CustomerInformation si ON si.CustomerNumber=c.DistributorId AND si.RecordType IN ('SF','BH')
    INNER JOIN [BQM_Dev].dbo.FunctionalGroupings g on b.DistributorId=g.CustomerNumber
WHERE bc.IncludeInReporting=1 
    AND ri.DateModified between @LateStartDate and @SalesEndDate
    AND ri.InvoiceDate > @SalesCutOffDate

When I have changed the Select statement is '*', it works fine. The following joins may be where the problem is, as they are joining on fields that are int on one side and nvarchar on the other side. These are lines:

LEFT OUTER JOIN #CustomerInformation ci ON ci.CustomerNumber=c.DistributorId AND ci.RecordType='C'
        LEFT OUTER JOIN #CustomerInformation si ON si.CustomerNumber=c.DistributorId AND si.RecordType IN ('SF','BH')
        INNER JOIN [BQM_Dev].dbo.FunctionalGroupings g on b.DistributorId=g.CustomerNumber

So, ci.CustomerNumber in int but c.DistributorId is nvarchar, Also b.DistributorId is Nvarchar and g.CustomerNumber is int.

I have tried Convert, I am just not certain what to do. any advice would be helpful.

CodePudding user response：

One or more of your returned columns are attempting a conversion to INT. At a quick glance this would appear to be an implicit conversion, and likely one you're not expecting.

If you know your data set well enough you might be able to pick out the column(s) causing the issue. If you do not you could simply comment them out one by one until you can execute your code. The most recently commented column is the culprit.

Your options are then to determine the proper data type for that column and explicitly case it as such, or to remove the offending row(s) from your result set. Maybe you need another predicate in a join or where?

CodePudding user response：

"b.DistributorId is Nvarchar"

I'd guess that one of the values is "Dist00", which you are trying to compare with the int 0.

CodePudding user response：

I actually was able to get this work by making the following updates to the above SQL: I added the Convert function on the int table and cast it to nvarchar(6).

LEFT OUTER JOIN #CustomerInformation ci ON Convert(nvarchar(6), ci.CustomerNumber)=c.DistributorId AND ci.RecordType='C' LEFT OUTER JOIN #CustomerInformation si ON Convert(nvarchar(6), si.CustomerNumber)=c.DistributorId AND si.RecordType IN ('SF','BH') INNER JOIN [BQM_Dev].dbo.FunctionalGroupings g on b.DistributorId=Convert(nvarchar(6), g.CustomerNumber)