List:General Discussion« Previous MessageNext Message »
From:Graeme B. Davis Date:May 4 1999 3:36pm
Subject:WHERE clause
View as plain text  
Hello,


I have a select that does a formula like:

SELECT name1, name2, someformula*pi()+atan(blah) AS test FROM blah ...

why can't I use WHERE test < 100?  It gives me a 'unknown column in where
clause' error ...

I hope I don't have to insert into a temp table to do this... are there any
ways around this?  could I put the formula as the WHERE clause?  ie. WHERE
someformula*pi()+atan(blah) < 100

?

thanks,

Graeme

the actual query is:

select id, city, state, address1, zip, longname,
1.852*60*(((ACOS(SIN(radians(latitude))*SIN(0.677988)+COS(radians(latitude))
*COS(0.677988)*COS(-1.349561-(radians(longitude)))))/PI())*180) as kmdist
from venues order by kmdist;
+----+----------------+-------+--------------------------+------------+-----
---------------------------+-------------+
| id | city           | state | address1                 | zip        |
longname                       | kmdist      |
+----+----------------+-------+--------------------------+------------+-----
---------------------------+-------------+
|  9 | Tift County    | GA    | Tift County Fair         |            |
Tiftarea YMCA                  |        NULL |
|  7 | Fairfax        | VA    | 3779 Pickett Rd          | 22031      |
Fairfax Ice Arena              |  4.48950878 |
|  3 | Fairfax        | VA    | 10110 Commonwealth Blvd. | 22032      |
Laurel Ridge Elementary School |  4.68314212 |
|  1 | Fair Lakes     | VA    | 12450 Fair Lakes Circle  | 22033-3832 |
Techmatics                     |  5.28933181 |
|  5 | Centreville    | VA    | Cranoke Street           | NULL       |
Cranoke Tennis Courts          | 12.11102216 |
|  8 | Springfield    | VA    | 6800 Commercial Drive    | 22151      |
Fairfax Sportsplex             | 13.56532684 |
|  4 | Arlington      | VA    | 1300 N Quincy Street     | 22201      |
Washington Lee High School     | 19.23626168 |
|  2 | Washington     | DC    | 1600 Pennsylvania Ave NW | 20500-0001 |
White House                    | 29.53973106 |
|  6 | Berwyn Heights | MD    |                          |            |
Berwyn Heights                 | 37.88999401 |
+----+----------------+-------+--------------------------+------------+-----
---------------------------+-------------+

how can I say 'kmdist <= 50' or something like that?  I'm trying to do a
radius search

Thread
WHERE clauseGraeme B. Davis4 May
  • Re: WHERE clausePaul Wolstenholme4 May
    • Benchmark questionSuresh Rajagopalan4 May
      • Benchmark questionMichael Widenius11 May
  • Re: WHERE clauseChristian Mack4 May