0

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

  • What datatype is Mandator? I don't believe Mandator can ever = 096, it would have to be '096' or 96. You cannot have a leading 0 on an int. – Mark Sinkinson Mar 25 '14 at 14:29
  • it's a char(3). The two constraints [Mandator] = 096 and [ReferenceType] = 4001 work perfectly fine. I can test that by just select * from dbo.foobar.memounion where [Mandator] = 096 AND ReferenceType = 4001. That works perfectly fine and shows the correct row. – Mathew Mar 25 '14 at 14:34
  • So to me the problem has to be the [ReferenceType] = [ID] part. But I could be wrong of course. – Mathew Mar 25 '14 at 14:37
  • Ok, well if it's a char, it should really be wrapped in ''. I assume you've tried (AND [Reference] = CAST([ID] AS NVARCHAR(10)))? – Mark Sinkinson Mar 25 '14 at 15:00
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.