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

Chris Cameron-Mills · Accepted Answer · 2009-07-20 16:24:28Z

2

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

answered Jul 20, 2009 at 16:24

Chris Cameron-Mills

4,6571 gold badge29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Smith Over a year ago

I thought it had something to do with the nesting. This appears to have fixed the problem.

Ula Krukar · Accepted Answer · 2009-07-20 19:51:32Z

1

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.

edited Jul 20, 2009 at 19:51

answered Jul 20, 2009 at 16:28

Ula Krukar

13.1k20 gold badges53 silver badges65 bronze badges

1 Comment

Aaron Smith Over a year ago

It didn't affect the performance much. The performance was already pretty terrible, and getting a result is much better than getting an error.

FerranB · Accepted Answer · 2009-07-20 16:48:14Z

0

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.

edited Jul 20, 2009 at 16:48

FerranB

37k18 gold badges70 silver badges87 bronze badges

answered Jul 20, 2009 at 16:27

northpole

10.3k7 gold badges38 silver badges60 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Invalid Identifier

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related