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:ross@xxxxxxxxxxxxxxxxxxx] > Sent: Wednesday, December 01, 2004 4:54 AM > To: php DB > Subject: 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
