Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    9
    Thanks
    0
    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.

    Thanks

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    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/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •