[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
Re: [PHP-DB] Use of 'as' name in where sub-clause
On Wednesday 01 December 2004 17:53, Ross Honniball wrote: If someone can give me a good reason why this is so, it will stop it annoying me so much. I'm sure someone on the _MYSQL_ list will give you a jolly good reason or two. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Optimism is the content of small men in high places. -- F. Scott Fitzgerald, The Crack Up */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Use of 'as' name in where sub-clause
Very simply, because a clause WHERE sales * cost 100 would look up to sales and cost in the expression as SELECT fields. Your clause will wotk if you add sales and cost to the SELECT fields, but that would not be economical: - you don't need them in the result - you will execute the caclulation twice In any case it good personal hygiene to use aliases Ignatius _ - Original Message - From: Ross Honniball [EMAIL PROTECTED] To: php DB [EMAIL PROTECTED] Sent: 01 December 2004 10:53 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
RE: [PHP-DB] Use of 'as' name in where sub-clause
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
Re: [PHP-DB] Use of 'as' name in where sub-clause
Ross, If someone can give me a good reason why this is so, it will stop it annoying me so much. From the MySQL manual for version 4.0.16: It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias. I didn't check section A.5.4, but it probably gives a more detailed explanation. dave