user5740086 user5740086 - 7 months ago 9
SQL Question

"Invalid column name" error in SQL

What's wrong with this code?

I get this error:


Invalid column name 'Distance'


Code:

SELECT
Company.CompanyId as Id,
( 6371 * acos( cos( radians(47.8423155) ) * cos( radians( Company.Latitude ) ) * cos( radians( Company.Longitude ) - radians(35.232933) ) + sin( radians(47.8423155) ) * sin( radians( Company.Latitude ) ) ) ) AS Distance
FROM
Company
INNER JOIN
Product ON Company.CompanyId = Product.CompanyId
WHERE
Distance< 5000
ORDER BY
Distance

Answer

Wrap the select inside subselect

   select * from(SELECT 
    Company.CompanyId as Id,
    ( 6371  * acos( cos( radians(47.8423155) ) * cos( radians( Company.Latitude  ) ) * cos( radians( Company.Longitude  ) - radians(35.232933) ) + sin( radians(47.8423155) ) * sin( radians( Company.Latitude ) ) ) ) AS Distance
FROM 
    Company 
INNER JOIN  
    Product ON (Company.CompanyId = Product.CompanyId) 
) AS P
WHERE
    P.Distance< 5000   
ORDER BY
    P.Distance;

The inner subquery returns the function on basis of which you are filtering with the alias Distance.