So is it possible to show the results from the inner query? I need to
see the p_no from the outer query and the p_no from the inner one.
Will a join allow me to do this? Also if I'm comparing the same table
to itself...wouldn't I use an exists instead of an outer join?
Thanks
Tom
Sybrand Bakker wrote:
> 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
Received on Wed Sep 20 2006 - 08:30:05 CDT
