Using alias in the where clause

[titans4771]

I have the below select statement:

Select o.orderid,
firstname,
'SUPERVISOR' = MAX(CASE when userdefinedfieldid = 83 and userdefinedanswer = 'Yes' then 'Y'
ELSE 'N' END),

Where SUPERVISOR = 'N'

I get the following error:
Invalid column name 'SUPERVISOR'

even if I enter it like this:
MAX(CASE when userdefinedfieldid = 83 and userdefinedanswer = 'Yes' then 'Y'
ELSE 'N' END) as supervisor

I still get the same error. How can I alias column names or formulas and be able to use them in a where clause?

Thanks!!

 

[markros]

There was a recent thread on the same topic. In MS SQL Server you can only reference calculated columns in the ORDER BY clause.

Also, are you using GROUP BY in your query? Can you post your query in full?

To solve your immediate problem you can do

select * from (my Current query without where) X where Supervisor = 'N'

 

[titans4771]

Select o.orderid,
firstname,
lastname,
'EMAIL' = isnull(email, email2),
'SUPERVISOR' = MAX(CASE when userdefinedfieldid = 83 and userdefinedanswer = 'Yes' then 'Y'
ELSE 'N' END),
'LOCATION LONG TITLE' = Case when oc.workfromhome = 1 then pa.municipality+', '+r.regioncode
ELSE p.municipality+', '+r2.regioncode END,
'LOCATION ABBREVATION' = Case when oc.workfromhome = 1 then r.regioncode
ELSE r2.regioncode END,
'GROUP LONG TITLE' = '',
'GROUP ABBREVIATION' = '',
c.candidateid --branchdescription,

from
candidates c
inner join ordercandidates oc on oc.candidateid = c.candidateid
inner join orders o on o.orderid = oc.orderid
inner join clientbranch cb on cb.branchid = o.branchid
left outer join userdefinedanswers u on u.entityid = o.orderid and u.entitytypeid = 4
left outer join postaladdress pa on pa.entityid = c.candidateid and pa.entitytypeid = 1
left outer join region r on r.regionid = pa.regionid
left outer join postaladdress p on p.entityid = cb.branchid and p.entityid = 7
left outer join region r2 on r2.regionid = p.regionid
where
(
((r2.regioncode in ('CA','CT') or (r.regioncode in ('CA','CT') and oc.workfromhome = 1))
and userdefinedfieldid = 83 and userdefinedanswer = 'Yes')
or
(r2.regioncode = 'ME' or (r.regioncode = 'ME' and oc.workfromhome = 1))

and oc.startingdate between @startdate and @enddate
)



Group by c.candidateid, firstname, lastname, email, email2, p.municipality, pa.municipality,
workfromhome, r.regioncode, r2.regioncode, o.orderid
Order by r.regioncode

 

[markros]

You can use suggestion from my previous reply - I also suggest you to use aliases for every field in a query - at least for documentation purpose (say, I can not tell by just looking which table which field belongs to). You may only select and group by the necessary fields in the derived table and add extra joins / fields / conditions outside.

 

[titans4771]

Thanks!

 

[markros]

No problem