T-sql – Comparing INT and NVARCHAR

t-sql

T-SQL newbie here. I am sure you guys can help me with a little problem I'm having.

Here's my situation:

I want to do a select from a certain table. And with that select I want to display the count of a referenced entry in another table.

So here's my script:

/****** Skript für SelectTopNRows-Befehl aus SSMS ******/
SELECT [ID]
  ,[ExpenseNumber]
  ,[Mandator]
  -- some more rows here

  -- Memo
  ,(SELECT COUNT(*)
    FROM [Foobar].[dbo].[MemoUnion]
    WHERE ([Mandator] = 096)
    AND ([ReferenceType] = 4001)
    AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID])) AS [MemoCount]
    FROM [Foobar].[dbo].[Expense]
    WHERE Mandator = 096 AND ExpenseNumber = 1;

So here's my problem, the column [MemoCount] shows 0, although I am 100% sure that there is an actual entry in dbo.MemoUnion where Reference = ID is actually true.

My suspicion is that it's not working because [Reference] is nvarchar and [ID] is an int.

So I fiddled around with CONVERT() and CAST(). But the result stays 0 none the less.

I did not get any error messages while using CONVERT and/or CAST. Just always a result of 0.

Any tips?

Thanks in advance

Best Answer

I solved it myself. In case someone runs into a similar problem: [dbo].[MemoCount] also has an ID Column. so i changed

AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID]))

to:

AND ([Reference]  = [Foobar].[dbo].[Expense].[ID])) AS [MemoCount]

Apparently SQL took the column [dbo].[MemoUnion].[ID] to compare it with [Reference]. That problem was of course not solvable for you guys because I never posted the Columns of [dbo].[MemoUnion] in the first place. Thanks for all the suggestions nonetheless!

Related Question