Correlated Subquery - invalid column error 1

[hazelsisson]

Hi guys,

I have two tables:

cm_std_locations
loc_code char(10)
loc_desc varchar2(50)

cm_std_distance
loc1 char(10)
loc2 char(10)
distance number

I have a query as follows:

select l.loc_code, l.LOC_DESC, d.distance
from   cm_std_locations l,
	   (select s.distance 
	   from cm_std_distance s
	   where (s.loc1 = :var and s.loc2 = l.loc_code) or (s.loc1 = l.loc_code and s.loc2 = :var) ) d
order by l.loc_desc;

:var is the variable I'll pass to the query (to be used in a cursor).

I'm getting the ORA-00904: invalid column name error, pointing at l.loc_code in the subquery.

Can anyone spot what I'm doing wrong?

Many thanks,
Hazel

 

[dbtoo2001]

Why do you want an inline view?

select l.loc_code, l.LOC_DESC, s.distance
from cm_std_locations l,
cm_std_distance s
where (s.loc1 = :var and s.loc2 = l.loc_code)
or (s.loc1 = l.loc_code and s.loc2 = :var)
order by l.loc_desc;

 

[hazelsisson]

I seem to have a talent for compicating things!

Thanks very much for your help dbtoo2001, it's just what I needed.

Cheers
Hazel