SQL Statement Problem [Archive]

fazthegreat

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.

Thanks

nikkiH

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.