Hi Erik,
No, Roger's method can't use an index. :-) But yes, using WHERE is
better than HAVING.
To get the WHERE to use an index, don't use a function in the
comparison:
SELECT invno, invdate, invamt, LEFT(invdate, 2) AS month
FROM salesfile
WHERE invdate LIKE '01%';
And I would guess that LIKE will be faster than the LEFT() function even
if there is no index.
Matt
----- Original Message -----
From: "Erik Osterman"
Sent: Tuesday, November 11, 2003 6:39 AM
Subject: RE: Aliases
However, you can use HAVING. HAVING is post-processed, in a brute force
method (no indexes can be used).
Select invno, invdate, invamt, left(invdate,2) as month from
salesfile HAVING month = '01'
But I would use Roger's example since it can take advantage of indexes.
Regards,
Erik Osterman
-----Original Message-----
From: Roger Baklund
Sent: Sunday, November 09, 2003 6:59 AM
Cc: David Katz
Subject: Re: Aliases
* David Katz
> I am trying to do a select statement where I am using one of the
> aliases in the where clause. I keep getting an error that the field
> does not exist.
>From the manual: "Note that standard SQL doesn't allow you to refer to
an
alias in a WHERE clause. This is because when the WHERE code is executed
the
column value may not yet be determined."
<URL: http://www.mysql.com/doc/en/Problems_with_alias.html >
> example:
>
> Select invno, invdate, invamt, left(invdate,2) as month from
> salesfile where
> month = '01'
>
> MySql keeps telling me that month is not a field. Is there a way to
do
> this?
Yes, simply repeat the expression:
Select invno, invdate, invamt, left(invdate,2) as month
from salesfile
where left(invdate,2) = '01'
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
