nested identifier

[tawright915]

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

[fitzj...@cox.net]

You're not 'nesting', that query is in an EXISTS condition. As such
you cannot use any part of its select list in the main SELECT.

David Fitzjarrell

[Sybrand Bakker]

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

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

[tawright915]

Thanks
I'm still working on my verbage. Sorry for calling it nesting. Okay
I'll try an outter join and see if that works for me.

Tom

[tawright915]

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]

On 20 Sep 2006 06:30:05 -0700, "tawright915" <tawri...@gmail.com>
wrote:

>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

As from this post I no longer respond to top-posted contributions.

[tawright915]

You my friend (and I mean that in the loosest of terms) are an arrogant
prick! I've looked at all your posts over the past several hours and
each time you have nothing constructive to say. If you are so bothered
by helping people out that are trying to gain a foothold in this area,
then STOP POSTING HERE!

Not everyone's opinion is necessarily yours, so don't assume that it
is, and while there are some people out there that wish to help out
people like myself, they would be better off if you didn't taint their
reputation by acting like a jerk!
I personally feel sorry for the people you work for and with.

I could care less if I were top post or bottom, for whatever that mean,
I just want to learn something new.

By the way before you start throw mud at people who you claim do not
know proper English, you should take a lesson yourself, you self
centered, egotistical, access programming idiot.

Note to everyone else: The opinions expressed here are not necessarily
those of Google, nor those who post on Google. I just want everyone to
know how much I loath, those to berate others (and before you think it,
I am and was justified) and never have anything good to say. I have
never had anything bad to say to anyone posting in Google groups,
neither here nor in the other groups that I belong too. However this
dude was the straw that broke the camels back, and it needed to be
said.

Tom

[Frank van Bortel]

tawright915 schreef:
whatever

Stop top posting, please.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

[tawright915]

I'll keep it short and sweet...for dweebs like you.....blow me
Tom

By the way....if you have nothing intelligent to contribute...then shut
up, but I guess you'll reply to this anyway.

Frank van Bortel wrote

[Sybrand Bakker]

On 21 Sep 2006 13:44:04 -0700, "tawright915" <tawri...@gmail.com>
wrote:

>I'll keep it short and sweet...for dweebs like you.....blow me
>Tom

So who is an arrogant prick? Please go jerk yourself and don't come
back.

[DA Morgan]

tawright915 wrote:
> I'll keep it short and sweet...for dweebs like you.....blow me
> Tom

Well you most certainly impressed us with your level of professionalism.

This group, recently, has deteriorated into a level of language far more
appropriate among adolescent males trying to impress their friends with
just how adult they are?

If you can not post something civil please don't post at all.

If unable to restrain yourself at least show us the courtesy of taking
it off-line.

Thank you.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group