Using Alias in Where
-
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. I keep getting "Invalid Column Name" error for Distance.
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!!
-
You could try a CTE.
WITH CTE AS (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
INNER JOIN
FoodType
ON (Member.FoodTypeID = FoodType.FoodTypeID)
WHERE
Zoom > 10
AND MAct = 'Full'
ORDER BY Distance ASC)
SELECT
*
FROM
CTE
WHERE
Distance < 20000;
p.s. Are you aware of the pitfalls of using NOLOCK?
-
SELECT TOP 5 ID, MID, RName, Pic1, FoodType, Average_P_PP, lat, lng, DistanceFROM (
SELECT ID, MID, RName, Pic1, FoodType.Descr AS FoodType, Average_P_PP, lat, lng, Zoom, MAct, (
SELECT GEOGRAPHY::Point(Lat, Lng, 4326).STDistance(GEOGRAPHY::Point(- 1.33821478, 36.71208143, 4326))
) AS Distance
FROM Member
INNER JOIN FoodType ON (Member.FoodTypeID = FoodType.FoodTypeID)
) a
WHERE Zoom > 10 AND Distance < 20000 AND MAct = 'Full'
ORDER BY Distance ASC;
Also, read through the links in my signature after the comment "If you litter your database queries with nolock query hints, are you aware of the side effects?".
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thank you that looks like it might do the trick.
And no I'm not aware of the pitfalls of No Lock could you please elaborate?
Also what is a CTE. Is this just a variable you've used?
-
BlackIceAngel (9/27/2013)
Thank you that looks like it might do the trick.And no I'm not aware of the pitfalls of No Lock could you please elaborate?
Also what is a CTE. Is this just a variable you've used?
CTE = common table expression
It's the SELECT query in the WITH clause.
It's a more convenient way to write subqueries.
Using Common Table Expressions
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
well your answer lies in the logical execution of your query.
The sql executes query in this order.
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
since, where clause is executed earlier then your select list, u can't provide alias name in your where clause, because for where clause part,,,, THAT ALIAS name doesn't exists.
That, brings us to one more thing that "u can use alias name in your order by clause,,,,(reason same,, execution order)" 🙂
-
BlackIceAngel (9/27/2013)
And no I'm not aware of the pitfalls of No Lock could you please elaborate? -
I prefer CROSS APPLY(s) to CTE(s) for aliasing. For example:
Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
ca1.Distance
From Member WITH(NOLOCK)
INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID)
CROSS APPLY (
SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326)) As Distance
) AS ca1
Where Zoom >10 AND ca1.Distance < 20000 AND MAct='Full'
Order By ca1.Distance ASC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I agree here with the CTE or CROSS APPLY. Otherwise you would have to but your select in your WHERE clause which is in my opinion is bad coding. :w00t:
I haven't done much with the CROSS APPLY and I really should. However I have used CTEs in a great deal. The difference I'd take is put only the information necessary in the CTE, the ID to reference the information in the CTE and the calculated field (Distance) and join it back along with your other tables (Member & FoodType) with an inner join.
Sort of like this:
;with CTE (ID, Distance) as (
select TOP 5 ID
, (SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance
from Member
where Zoom >10
AND MAct='Full'
)
Select ID
, MID
, RName
, Pic1
, FoodType.Descr AS FoodType
, Average_P_PP
, lat
, lng
, cte.Distance
From Member with(nolock) inner join
FoodType with(nolock) on (Member.FoodTypeID = FoodType.FoodTypeID) inner join
CTE on Member.ID = CTE.ID
Where CTE.Distance < 20000
order by CTE.Distance ASC
Not exactly sure where your columns are coming from. I typically will alias the tables and reference ALL of my columns with the alias IE:
select top 2 Member.ID,Member.MID
.
.
.
.
I tend to stress this with all my developers so it just makes things a little easier to follow in the future.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply