Oracle SQL Invalid Identifier

Question

I'm trying to run this query but I get "ORA-00904: "Z1"."LONGITUDE": invalid identifier"

Is there a way to rewrite this so I have access to that column in the exists subquery? Or is there generally a better way to achieve what I'm trying to do?

Thanks

select zip, count(UNIQUE address_id) LOCATIONS
from records 
inner join addresses a using(address_id) 
inner join zip_coords z1 using(zip)
where exists
(
  select 1 from (
    select distance(z1.latitude, z1.longitude, z2.latitude, z2.longitude) d
    from zip_coords z2
    where z2.zip in (
      select zip from available_zips
    )
  ) where d <= 50
)
GROUP BY ZIP

Answer 1

Your problem is that you can't descend that many levels into your subquery. I might have missed something from skimming over your query but could:

select 1 from (
    select distance(z1.latitude, z1.longitude, z2.latitude, z2.longitude) d
    from zip_coords z2
    where z2.zip in (
      select zip from available_zips
    )
  ) where d <= 50

not be rewritten as:

SELECT 1
FROM zip_coords z2
WHERE z2.zip IN (
  SELECT zip FROM available_zips
)  
AND distance(z1.latitude, z1.longitude, z2.latitude, z2.longitude) <= 50

Answer 2

instead of using:

inner join zip_coords z1 using(zip)

Try including zip_coords z1 as part of the FROM clause and include the joins in the WHERE. You should then be able to access z1 from your subquery.

Answer 3

select zip, count(UNIQUE address_id) LOCATIONS
from records 
inner join addresses a using(address_id) 
inner join zip_coords z1 using(zip)
where 
(
    select min(distance(z1.latitude, z1.longitude, z2.latitude, z2.longitude)) d
    from zip_coords z2
    inner join available_zips using(zip)
) <= 50
GROUP BY ZIP

I have to warn you, I do not know how this will affect performance of the query.