Eray Balkanli Eray Balkanli - 22 days ago 7
SQL Question

How to use a newly defined column name in the where clause of a query in sql 2008

I have a query in sql server 2008 like:

select t1.Id,
MatchIds = (select dbo.GetCommaDelimitedString(t2.Id) from #temp t2
where t1.Id != t2.Id and t1.PatientId=t2.PatientId
and t1.HCN=t2.HCN
)
from #temp t1


This query has an output like:

Id MatchIds
1 2,5,6
2 1,5,6
3 null
4 null
5 1,2,6
6 1,2,5


What I want is to get rid of the rows whose MatchIds is null. When I try to add
MatchIds is not null
with a where clause to the main query, it is not accepting it saying that
invalid column name MatchIds
and I do not want to write the same query I used to assign MatchIds in the where clause as well. In this case, what is the best way to provide it?

Any help would be appreciated.

Answer

Just move query to CROSS APPLY

select t1.Id, 
       cs.MatchIds
from #temp t1
CROSS APPLY (select dbo.GetCommaDelimitedString(t2.Id) from #temp t2
       where t1.Id != t2.Id and t1.PatientId=t2.PatientId 
       and t1.HCN=t2.HCN) cs (MatchIds)