CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Somejerk
Somejerk

Reputation: 193

Seeking SQL syntax with errors using HAVING with assigned variable

I am looking for the SQL syntax to use HAVING in the following statement:

DECLARE @ORIG_LAT AS FLOAT = 40.4882011413574; 
DECLARE @ORIG_LONG AS FLOAT = -80.1939010620117;
DECLARE @DISTANCE AS INT; 

SELECT LATITUDE_DEG, LONGITUDE_DEG,SQRT( 
    POWER(69.1 * (LATITUDE_DEG - @ORIG_LAT), 2) + 
    POWER(69.1 * (@ORIG_LONG - LONGITUDE_DEG) * COS(LATITUDE_DEG / 57.3), 2)) AS DISTANCE 
FROM NAVAIDS 
HAVING DISTANCE < 80 --error 
ORDER BY DISTANCE ASC;

Error:

Msg 207, Level 16, State 1, Line 9
Invalid column name 'distance'.

It's ok with the ORDER BY but I don't understand why it doesn't like the HAVING. Any help with direction? It is SQL Server 2008 R2

Upvotes: 1

Views: 73

Answers (2)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171431

select *
from (
    SELECT LATITUDE_DEG, LONGITUDE_DEG, SQRT( 
        POWER(69.1 * (LATITUDE_DEG - @ORIG_LAT), 2) 
        POWER(69.1 * (@ORIG_LONG - LONGITUDE_DEG) * COS(LATITUDE_DEG / 57.3), 2)
    ) AS DISTANCE 
    FROM NAVAIDS 
) a 
WHERE DISTANCE < 80 
ORDER BY DISTANCE

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

As you have noticed, you can't use an aliased column directly. The easiest solution would be to wrap your statement in a subselect and apply your clause on that.

SELECT  *
FROM    (
          SELECT  LATITUDE_DEG
                  , LONGITUDE_DEG
                  , SQRT(
                      POWER(69.1 * (LATITUDE_DEG - @ORIG_LAT), 2) +
                      POWER(69.1 * (@ORIG_LONG - LONGITUDE_DEG) * COS(LATITUDE_DEG / 57.3), 2)) AS DISTANCE
          FROM    NAVAIDS
        ) q          
WHERE DISTANCE < 80
ORDER BY 
        DISTANCE ASC;

Upvotes: 2

Related Questions