Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: SQL Statement Problem
02-12-2007, 02:49 AM #1
- Join Date
- Jan 2007
- Thanked 0 Times in 0 Posts
SQL Statement Problem
Hey guys, I'm tryin got execute this statement:
cmd.CommandText="Select zip, zipcode_name, Avg(3958.75 * ACos(Sin(Latitude/57.2958) * Sin(Latitude/57.2958) + Cos(Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - Longitude/57.2958))) As Miles From tbl_zips where Miles >=(3958.75 * ACos(Sin(Latitude/57.2958) * Sin(Latitude/57.2958) + Cos(Latitude/57.2958) *Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - Longitude/57.2958))) And Zip != ZipCode;"
scary right! but I keep on getting the error "invalid column Miles". Does anyone know why can that be happening.
02-12-2007, 03:52 PM #2
- Join Date
- Jun 2005
- Near Chicago, IL, USA
- Thanked 32 Times in 31 Posts
'miles' is an alias of a computed value.
You can't do that. At least not like this.
You have several options, including a calculated column in the table itself (instead of the select), a derived view, a normal view, a stored procedure, or just one big sub-select. It depends on the full query and what access you have to the back end.
If possible, I'd make those computed values actual columns in the table itself (to make use of indexing) or a view. Or a stored procedure. Anything but this; this is going to not perform well at all once scaled and live.
If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/