| Miles Thompson 2004-08-29, 3:55 am |
| 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
|