How use case statement alias in where clause

I’m trying to use a case statement and the results to in an alias, but I need to use the alias in my where clause and this doesn’t seem to be working. How do I use the alias (isPrimary below) in my where clause. See comment where I’m trying to use isPrimary in my where clause, which doesn’t work. How do I use the alias in the where clause?

CREATE TABLE #cases
(
   id varchar(25),
    CASEID VARCHAR(12)
)

#cases:
id  caseid
15  12345
15  23456



CREATE TABLE #services
(
    id varchar(25),
    CASEID VARCHAR(12),
    createdate VARCHAR(30),
    types int
)

#services:
id    caseid    createdate               types
15    12345     2021-04-27 11:59:01.333  null    --this is the primary one
16    12345     2021-04-28 07:37:20.163  null
17    12345     2021-04-28 07:55:08.750  10

select c.caseid,
CASE WHEN sv.id = (SELECT Top 1 ID FROM #services WHERE caseid = c.caseid ORDER BY createdate ASC) THEN 1 ELSE 0 END AS isPrimary  --if lowest date for sv.caseid then label "1", otherwise "0"
from
#cases c
left join #services sv on sv.caseid=c.caseid
where 
(isPrimary=0 and types is null) --it doesn't want me to use the alias here

I was looking at [case alias where][1] but it doesn’t try to use the alias in the where clause. I don’t see how to do that in my search. I need to return the null “types” that aren’t primary. There are multiple cases, not just the one in the services table.

Answer

You could do your initial query as a CTE to get the alias and then use your WHERE condition on that resulting data set.

;with cte
as (
    select c.caseid, types,
    CASE WHEN sv.id = (SELECT Top 1 ID FROM #services WHERE caseid = c.caseid ORDER BY createdate ASC) THEN 1 ELSE 0 END AS isPrimary  
    from
    #cases c
    left join #services sv on sv.caseid=c.caseid)
select *
from cte
where 
(isPrimary=0 and types is null)

Advertisement

Answer