Using aliases in WHERE clause

#1
kborkowski
kborkowski
Joined on 2011‑04‑05

Hi,

I'm trying to run query : 

SELECT DISTINCT ppi.inquiries AS i FROM ProjectPartInfo ppi WHERE i.status IS NOT NULL

Unfortunately, 'i' alias doesn't work in WHERE clause : 

Query Execution Error
=====================
Unexpected query token 'i'
SELECT DISTINCT ppi.inquiries AS i FROM ProjectPartInfo ppi WHERE  ==> i <== .status IS NOT NULL

 

When i change 'i' to 'ppi.inquiries' in my WHERE statement, query runs correctly : 

SELECT DISTINCT ppi.inquiries AS i FROM ProjectPartInfo ppi WHERE ppi.inquiries.status IS NOT NULL

However, 'i' alias works in ORDER clause (query below also runs correctly) : 

SELECT DISTINCT ppi.inquiries AS i FROM ProjectPartInfo ppi ORDER BY i.id

 

Is this a bug or should I avoid using aliases like this in WHERE statements ?

#2
support
support
Joined on 2010‑05‑03

The processing order of a query is: 

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

Accordingly, in WHERE you can use FROM variables but not SELECT aliases.

SELECT aliases can be used only in the ORDER BY clause. 

ObjectDB Support

Reply