Compare calculated value in where clause
-
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.
-
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)
-
Thanks, Roland. It works perfectly!
-
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.DistanceFrom tbl_Stores s
cross apply(select SQRT(POWER(s.Latitude - @Latitude, 2) + POWER(s.Longitude - @Longitude, 2))) as d(Distance)
Where d.Distance < 60
-
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.diffdatefrom 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:
-
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 ands.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.Distancefrom 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
[/url]Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply