0

I am trying to use a selected field's named alias in a where clause, yet the database complains about being unable to find said field.

Query:

SELECT somefunction(myfield) as mytest, myotherfield as mytest2
FROM database.table
WHERE mytest IS NULL OR mytest2 IS NULL

Expectation: The db would simply test the result of somefunction(myfield) or mytest for being NULL

Result: The db cannot find mytest or mytest2

Is there something i am missing; why would something like this not work?

Gung Foo
  • 13,392
  • 5
  • 31
  • 39

1 Answers1

4

SELECT somefunction(myfield) as mytest, myotherfield as mytest2 FROM table WHERE mytest IS NULL OR mytest2 IS NULL

EDIT: Sorry table.

Possible duplicate:

Using column alias in WHERE clause of MySQL query produces an error

Answer:

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Copied from http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Community
  • 1
  • 1
Jesper
  • 3,816
  • 2
  • 16
  • 24