Compare calculated value in where clause

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  August 22, 2011 at 9:29 pm

  #243725

  I have an application to calculate the distance between two addresses.

  Here is my SQL statement:

  "Select store_Name, latitude, longitude,

  SQRT(POWER(Latitude - @Latitude, 2) + POWER(Longitude - @Longitude, 2)) as Distance From tbl_Stores Where Distance < 60;"

  The error message states that "Invalid column name 'distance'." I think it does not recognize the aliasname. How to get around it?

  Also, since not every store has Latitude/Longitude values, some rows may return null value for calculated distance. However, I do need to keep those data rows even I could not run distance comparison. The final data would be something like this:

  1. Store1, 34.1, -118.7, 55

  2. Store2, 34.2, -118.6, 56

  3. Store3, null, null, null

  4.

  etc.

• Roland C

  Hall of Fame

  Points: 3473

  More actions

  August 23, 2011 at 12:06 am

  #1371774

  You can't use column aliases in WHERE clause. uses the plain expression to compare :

  Select store_Name, latitude, longitude,

  SQRT(POWER(Latitude - @Latitude, 2) + POWER(Longitude - @Longitude, 2)) as Distance

  From tbl_Stores

  Where (SQRT(POWER(Latitude - @Latitude, 2) + POWER(Longitude - @Longitude, 2)) < 60)

  or (SQRT(POWER(Latitude - @Latitude, 2) + POWER(Longitude - @Longitude, 2)) IS NULL)

• jay-125866

  SSCommitted

  Points: 1629

  More actions

  August 23, 2011 at 6:44 am

  #1371942

  Thanks, Roland. It works perfectly!

• Nils Gustav Stråbø

  SSChampion

  Points: 11259

  More actions

  August 23, 2011 at 11:02 am

  #1372162

  And here is a little "trick" to allow reuse of the calculated column by using CROSS APPLY

  Select s.store_Name, s.latitude, s.longitude, d.Distance

  From tbl_Stores s

  cross apply(select SQRT(POWER(s.Latitude - @Latitude, 2) + POWER(s.Longitude - @Longitude, 2))) as d(Distance)

  Where d.Distance < 60

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 23, 2011 at 11:18 am

  #1372166

  Hmmm.. interesting to see this... i tried simulating the question by using 3 variations , cte, cross apply ( from nils gustav ) and OP's method.

  Here is test code:

  select 1 a, crsapp.diffdate

  from sys.columns

  cross apply ( select DATEDIFF(dd, getdate() , getdate()-10) diffdate ) crsapp

  where crsapp.diffdate = 1

  ; with cte as

  ( select 1 a,DATEDIFF(dd, getdate() , getdate()-10) diffdate

  from sys.columns

  )

  select * from cte

  where diffdate = 1

  select 1 a ,DATEDIFF(dd, getdate() , getdate()-10) diffdate

  from sys.columns

  where DATEDIFF(dd, getdate() , getdate()-10) = 1

  To my surprise, all the 3 methods produce identical plans.. hmmm.. time to dig deep :w00t:

  [/url]

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  August 25, 2011 at 4:20 am

  #1373019

  Two issues :

  1) If you are using 2008, use the spatial functionality.

  2) The sql is non-sargable , every store has to be tested to see if the distance from the point is < 60. Fine if you are 'mom and pop' with 10 stores, not good if you a Walmart with a worldwide operation.

  You need to limit the amount of stores tested by adding to the where clause

  s.Latitude between @Latitude - 60 and @Latitude + 60 and

  s.Longitude between @Longitude - 60 and @Longitude + 60

  Math may not be spot on , but you get the idea.

  To make full use of potential indexes on longitude and latitude , i would use a self join ie (untested)

  select slat.store_Name, slat.latitude, slat.longitude, d.Distance

  from tbl_Stores slat

  join tbl_Stores slong

  on slat.storeId = slong.storeid

  cross apply(select SQRT(POWER(slat.Latitude - @Latitude, 2) + POWER(slong.Longitude - @Longitude, 2))) as d(Distance)

  Where d.Distance < 60

  and slat.latitude between @Latitude -60 and @Latitude +60

  and slong.longitude between @longitude -60 and @longitude +60

  ---

  
  Clear Sky SQL
  My Blog[/url]