Ross,
That's correct. From the MyQL manual:
Begin quote ......
In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement will not work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
The reference to b in the HAVING clause refers to an alias for an expression in the SELECT list that uses @aa. This does not work as expected: @aa will not contain the value of the current row, but the value of id from the previous selected row.
The general rule is to never assign and use the same variable in the same statement.
....... end quote
There's more at http://dev.mysql.com/doc/mysql/en/Variables.html
So why not: select *, (StkhistMonthqty06 + StkhistMonthqty07 + StkhistMonthqty08) as total
.
. rest of statement ...
.
and total > 0
Regards - Miles Thompson
At 10:10 PM 8/28/2004, Ross Honniball wrote:
SQL Statement:
select *,@xtotal := StkhistMonthqty06 + StkhistMonthqty07 + StkhistMonthqty08 as total
from StkMast as sm inner join StkHist as sh using (STkCode)
where (StkAuthor like 'keller%')
and (sh.StkhistYear='2004')
and (@xtotal>0);
Notice the use of @xtotal.
I have saved some output from an instance where I ran this query and it worked as expected.
Subsequently it has stopped finding any results. (the table has definitly NOT changed).
If I take out the and (@xtotal>0) clause, it finds records (and, whatsmore, I can see that xtotal is indeed greater than zero.
Does anyone know of any erratic behaviour when using the assignment operator?
Or am I doing something wrong?
(Note that this is actually the first time I have ever used the assignment operator, so I'm pretty green really)
.
. Ross Honniball. JCU Bookshop Cairns, Qld, Australia.
.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
