SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Alias in WHERE using geography functionality
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BlackIce662
Starting Member

South Africa
11 Posts

Posted - 09/27/2013 :  04:03:54  Show Profile  Reply with Quote
Good Day

I would like to restrict my records to distances that are under a certain amount (I handle this later with coding) but the problem is setting the limit in my SQL.

This is my original code:
Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND Distance < 20000 AND MAct='Full' Order By Distance ASC


I also tried:
Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND Cast((SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) as int) < 20000 
AND MAct='Full' Order By Distance ASC


And I tried:
Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND (SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) < 20000 
AND MAct='Full' Order By Distance ASC


Thank you

Greatness... Live It!!

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 09/27/2013 :  07:18:28  Show Profile  Reply with Quote
How about using a cte to determine the distance before the query?

djj
Go to Top of Page

BlackIce662
Starting Member

South Africa
11 Posts

Posted - 09/27/2013 :  07:33:39  Show Profile  Reply with Quote
Thank you I just tried that and it works. I'll post the code soon

Greatness... Live It!!
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 09/27/2013 :  09:16:04  Show Profile  Reply with Quote
You are welcome.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000