user5740086 - 1 year ago 46

SQL Question

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 Source

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.