Re: nested identifier

On 19 Sep 2006 12:28:30 -0700, "tawright915" <tawright915_at_gmail.com> wrote:

>I'm trying this query:
>
>select distinct a.p_no, c.p_no
>from premise a, street_name b
>where a.k_str_nm = b.k_str_nm
>and exists(select '1'
> from premise c,
> street_name d
> where c.ky_str_nm = d.ky_str_nm
> and c.ky_prem_no <> a.ky_prem_no
> and c.ky_str_nm <> a.ky_str_nm
> and c.ad_serv_str_no = a.ad_serv_str_no);
>
>I get this error:
>SQL> @overlapping.sql
>select distinct a.p_no, c.p_no
> *
>ERROR at line 1:
>ORA-00904: "C"."P_NO": invalid identifier
>
>
>SQL>
>
>Why an I not able to see this identifier? I thought nesting it was
>okay?
>
>Thanks
>Tom

I wouldn't call this 'nesting'.
You have an outer query block (the main select) and an inner query block (the subquery).
You are referring to an inner block identifier in the outer block. However in the outer block this identifier is *out of scope*, hence you get ora-904.
To me it looks like, if the select list reflects the desired result, you can't use subquery, and you need to convert to outer join.

--
Sybrand Bakker, Senior Oracle DBA