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

Question

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 MatchIdsand 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.

Pரதீப் · Accepted Answer · 2016-11-17 16:10:20Z

3

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)

answered Nov 17, 2016 at 16:10

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2016-11-17 16:11:18Z

0

SELECT *
FROM (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) results
WHERE results.MatchIds IS NOT NULL;

Use your result as a subquery.

answered Nov 17, 2016 at 16:11

Jens

3,3092 gold badges28 silver badges43 bronze badges

Comments

Gareth Lyons · Accepted Answer · 2016-11-17 16:19:15Z

0

A CTE can also work:

WITH CTE_matches AS
(
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
)

SELECT Id, MatchIds FROM CTE_matches WHERE MatchIds IS NOT NULL

edited Nov 17, 2016 at 16:19

answered Nov 17, 2016 at 16:14

Gareth Lyons

1,97214 silver badges14 bronze badges

1 Comment

Jens Over a year ago

* IS NOT NULL

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related