MySQL alias, variables and the WHERE clause
What a nice morning i had remembering that you cannot reference an ALIAS or a variable in a WHERE clause. So for anyone else that has a memory like mine (i'm jealous of fish put it that way)
standard SQL doesn't allow you to refer to a column alias in a where cause. Which make total sense (now) as the column value is most likely not determined at time of execution. So....
SELECT
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
#WHERE
# @total > now() /*Invalid*/
#WHERE
# newDate > now() /*Invalid*/
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
#WHERE
# @total > now() /*Invalid*/
#WHERE
# newDate > now() /*Invalid*/
Comments (10) | 
Print |
Send |
16745 Views
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
HAVING @total > now()
#WHERE
# @total > now() /*valid*/
#WHERE
# newDate > now() /*Invalid*/
also what is the colon for? :=
SELECT
@total := date_add(dat.....
> i think you meant...
> #WHERE
> # @total > now() /*valid*/
No, I don't believe using a variable there is a truly valid solution. Variables change values when you least expect them to. You should only use variables as a way to store information *between* queries, not *within* a query, AFAIK.
A solution I've found elsewhere is that you can use the column alias in your HAVING clause. (http://dev.mysql.com/doc/refman/5.0/en/problems-wi...)
You can use HAVING instead of WHERE in this case, it works perfectly fine and the difference is :
[quote]
The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.
[/quote]
[refrence]
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...
[/refrence]
Hope it helps,
MajiD
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...