Yeah, I've asked this question myself a million times. It'd be very
useful and maybe some DB system knows how to do this, but for the most
part you just have to deal with this reality.
I believe that it's a matter of timing, really. You can't use the alias
in the WHERE clause because it hasn't been created yet.
I believe everything is evaluated and executed then when it comes to the
final output, it uses the aliases as column headers (if no alias is set,
it'll use the column name... Unless it's an aggregate function, then it
uses nothing.. Which doesn't work well when you do a sub-select as a
table.. But that's another story).
What you COULD do, although it can add a little (or a lot) of overhead
to your query:
SELECT total FROM (SELECT (sales * cost) as total FROM
sometable) t1 where t1.total 100
There may be some cases where you'd want to do this, but be aware that
it most likely executes like this:
1. Performs the SELECT (sales * cost) query on ALL rows in the
database.. If you have a million rows and only 10 of them have sales *
cost 100, it's going to be a major drag on your system to get those 10
rows. It MAY have perform the function on all rows anyway since it's a
math function. So this is maybe a drain if you do it as WHERE
(sales * cost) 100 but it's definitely a drain if you do it with the
sub-select I used above. Also, not sure, but if you're using MySQL,
I'm not sure that it supports it below version 4.1 (or was that another
database I worked with recently? I forget).
2. After performing the total on everything, then it evaluates the
SELECT total... WHERE t1.total 100 on the result set. It treats the
sub-select as if it were a table, using it's results to select from.
Some just DB trivia for ya. If you figure out a way to use an alias in
your WHERE clause without doing a sub-select, let me know. :)
-TG
-Original Message-
From: Ross Honniball [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 4:54 AM
To: php DB
Subject: [PHP-DB] Use of 'as' name in where sub-clause
Hi All,
In MySql, you CAN'T do the following:
SELECT sales * cost AS total WHERE total 100
Instead, you need to say:
SELECT sales * cost AS total WHERE sales * cost 100
I'm just curious WHY you can't do it. You can, for example,
specify ORDER
BY total.
I can't think of any reason why it would be either difficult
or ambiguous
for the SQL engine to allow you to use your calculated field
name in the
WHERE part of the query.
If someone can give me a good reason why this is so, it will stop it
annoying me so much.
Regards ... Ross
.
. 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
