View Full Version : SQL Statement Problem

02-12-2007, 02:49 AM
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
'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.