On Tue, Mar 9, 2010 at 10:42 AM, Michael Wood <esiotrot@gmail.com> wrote:
[snip]
> I don't know why the above doesn't work. I've encountered something
> in the past which may be related:
>
> SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
> FROM mytable
> WHERE something IS NOT NULL
> AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
> AND other = 123;
>
> This works, but what I want to do is the following:
>
> SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
> FROM mytable
> WHERE something IS NOT NULL
> AND blah <> ''
> AND other = 123;
>
> This does not work and I don't know why not.
This behavior is mandated by the SQL standard, I believe. I'm too lazy
to dig up the actual reference, but for instance
http://dev.mysql.com/doc/refman/5.0/en//problems-with-alias.html
claims:
Standard SQL disallows references to column aliases in a WHERE clause.
This restriction is imposed because when the WHERE clause is evaluated,
the column value may not yet have been determined...
You could workaround by using a subquery like:
SELECT mysubq.blah FROM (
SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
FROM mytable
WHERE something IS NOT NULL
AND other = 123
) AS mysubq
WHERE mysubq.blah <> '' ;
Josh