pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Bounds checking on an alias 2+ messages / 2 participants [nested] [flat]
* Bounds checking on an alias @ 2000-12-19 01:11 jkakar@expressus.com 0 siblings, 1 reply; 2+ messages in thread From: jkakar@expressus.com @ 2000-12-19 01:11 UTC (permalink / raw) To: pgsql-sql Hi, Removing the double single-quotes from the distance function fixed the problem, thanks. I've now encountered another problem... I have the following (ugly) query: SELECT DISTINCT tbl_restaurant.restaurant, tbl_restaurant_location.postal_code, tbl_restaurant_location.latitude AS latitude, tbl_restaurant_location.longitude AS longitude, distance (49.24894, -122.90419, latitude, longitude) AS distance FROM tbl_restaurant, tbl_restaurant_location, tbl_restaurant_cuisine_link WHERE tbl_restaurant.restaurant_id = tbl_restaurant_location.restaurant_id AND tbl_restaurant.restaurant_id = tbl_restaurant_cuisine_link.restaurant_id AND tbl_restaurant_cuisine_link.cuisine_id = 14 AND tbl_restaurant.price_id = 1 AND tbl_restaurant_location.latitude IS NOT NULL AND tbl_restaurant_location.latitude > 49.113804 AND tbl_restaurant_location.latitude < 49.384075 AND tbl_restaurant_location.longitude > -123.03932 AND tbl_restaurant_location.longitude < -122.76906 AND distance <= 15.0 ORDER BY distance; What I'm doing is selecting all restaurants of a particular cuisine and within a particular price range. Out of the restaurants that meet those criteria I only want the ones within a certain range of my location- in this case, 49.24894, -122.90419. The way I narrow down the search results is to select only those restaurants within certain lat/lon points; basically, I'm selecting restaurants that fall in a square region with my location in the middle. Then, I'm applying my distance formula to filter these results. When I try and do a "distance <= 15.0" I get: ERROR: Attribute 'distance' not found If I remove that particular clause it works fine but my results aren't as accurate as they need to be. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. jkakar@expressus.com 708-1641 Lonsdale Avenue V: (604) 988-6999 North Vancouver, BC, V7M 2J5 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bounds checking on an alias @ 2000-12-19 14:45 Alessio Bragadini <alessio@albourne.com> parent: jkakar@expressus.com 0 siblings, 0 replies; 2+ messages in thread From: Alessio Bragadini @ 2000-12-19 14:45 UTC (permalink / raw) To: jkakar@expressus.com; +Cc: pgsql-sql jkakar@expressus.com wrote: > SELECT DISTINCT tbl_restaurant.restaurant, > tbl_restaurant_location.postal_code, tbl_restaurant_location.latitude > AS latitude, tbl_restaurant_location.longitude AS longitude, distance > (49.24894, -122.90419, latitude, longitude) AS distance FROM > tbl_restaurant, tbl_restaurant_location, tbl_restaurant_cuisine_link > WHERE tbl_restaurant.restaurant_id = > tbl_restaurant_location.restaurant_id AND tbl_restaurant.restaurant_id > = tbl_restaurant_cuisine_link.restaurant_id AND > tbl_restaurant_cuisine_link.cuisine_id = 14 AND > tbl_restaurant.price_id = 1 AND tbl_restaurant_location.latitude IS > NOT NULL AND tbl_restaurant_location.latitude > 49.113804 AND > tbl_restaurant_location.latitude < 49.384075 AND > tbl_restaurant_location.longitude > -123.03932 AND > tbl_restaurant_location.longitude < -122.76906 AND distance <= 15.0 > ORDER BY distance; > ERROR: Attribute 'distance' not found It's not related to your function or query, but it's a generic behaviour: changemaster=# select id + 1 as next, val from t; next | val ------+------ 2 | Test (1 row) changemaster=# select id + 1 as next, val from t where next > 1; ERROR: Attribute 'next' not found changemaster=# select id + 1 as next, val from t where id + 1 > 1; next | val ------+------ 2 | Test (1 row) The named expression isn't available in the WHERE clause. You have to copy the same expression in it. -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-12-19 14:45 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-12-19 01:11 Bounds checking on an alias jkakar@expressus.com 2000-12-19 14:45 ` Alessio Bragadini <alessio@albourne.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox