Use alias in OR clause SQL

Question

I am attempting to use a column alias in an OR clause, but I keep getting the error: "Invalid column name 'TEAM NAME'". I've seen threads about using aliases in WHERE clauses, but I'm not sure if OR is possible. Here's what I'm working with:

[TEAM NAME] = CASE WHEN MAX(L.City) IN ('RY','BR') THEN 'Team 1'
WHEN MAX(L.City) IN ('JS','ND') THEN 'Team 2' 
WHEN MAX(L.City) IN ('QV','VE') THEN 'Team 3'
WHEN MAX(L.City) IN ('FB','RX') THEN 'Team 4'
ELSE '-' END,

and here is my OR clause

AND (U.Player IN ('Tom Thumb','Wallace Gromit') OR [Team Name] <> '-')

I think your best bet is wrapping your main current query in a subquery and putting that part on the outside, if you want to use the alias. Alternatively, chuck that line in a having clause without using the alias. e.g. `having u.player in ('Tom Thumb','Wallace Gromit') or max(L.city) in ('ry','br','js','nd','qv','ve','fb','rx')` — ZLK, Jan 18 '17 at 21:37

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

Unfortunately you have to write it out. PS this should be in the HAVING clause:

AND (U.Player IN ('Tom Thumb','Wallace Gromit') OR 
CASE WHEN MAX(L.City) IN ('RY','BR') THEN 'Team 1'
WHEN MAX(L.City) IN ('JS','ND') THEN 'Team 2' 
WHEN MAX(L.City) IN ('QV','VE') THEN 'Team 3'
WHEN MAX(L.City) IN ('FB','RX') THEN 'Team 4'
ELSE '-' END,
 <> '-')

OR

AND (U.Player IN ('Tom Thumb','Wallace Gromit') OR 
MAX(L.City) NOT IN ('RY','BR','JS','ND','QV','VE','FB','RX'))

Alternatively you could treat your whole query as a Subquery and select from that

Select  * from (myquery) s where (U.Player IN ('Tom Thumb','Wallace Gromit') OR [Team Name] <> '-')

Similar issue here

Check out a basic example here

edited May 23 '17 at 11:45

Community

1
1

answered Jan 18 '17 at 21:37

EoinS

5,405
1
19
32

3

Note: Aggregates go in a `HAVING` clause. – ZLK Jan 18 '17 at 21:40
2

I am referring to the first two parts of your answer. They need to be in a having clause or they will still produce an error. – ZLK Jan 18 '17 at 21:45
@zlk smart, 100% correct. updated – EoinS Jan 18 '17 at 21:53
Your second solution worked well for me once I put it in the HAVING clause-- thanks a lot to both of you for the help!! – reggie86 Jan 18 '17 at 22:03

score 0 · Answer 2 · answered Jan 18 '17 at 21:47

Have you tried aliasing it this way in the SELECT statement?

CASE WHEN MAX(L.City) IN ('RY','BR') THEN 'Team 1'
     WHEN MAX(L.City) IN ('JS','ND') THEN 'Team 2' 
     WHEN MAX(L.City) IN ('QV','VE') THEN 'Team 3'
     WHEN MAX(L.City) IN ('FB','RX') THEN 'Team 4'
     ELSE '-' END AS [TEAM NAME]

If this is part of a subquery or CTE, it will provide you access to this column in the outer query request data from it.

Also, the WHERE clause is run before the SELECT clause in any query/subquery, so the alias will not be available in the WHERE clause if it was made in the SELECT clause at the same query/subquery.

Use alias in OR clause SQL

2 Answers2