Using aliases in WHERE clause

#1

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

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