Re: SQL - Should produce an error and doesn't, please help.

In article <32A83E3F.4F82_at_ae.ge.com>, Doug Moloney <Doug.Moloney_at_ae.ge.com> wrote:
>SELECT PartNo FROM table_1
> WHERE table1.Partno NOT IN
> (SELECT partno from publicview)
>
>The partno column in publicview is not a valid column name. Oracle
>should give me an error, but instead runs the query and returns no
>results. If I qualify the column as publicview.partno I get the error,
>but I have to qualify it to get the error. Anyone see this before?
>
>Please e-mail me directly at
>doug.moloney_at_ae.ge.com
>

Doug,

I'll bet dollars to donuts that partno is a valid column in table_1.

It's valid to reference the "outside" select's columns anywhere in the interior query. I think you've created a correlated query, and therefore you're running a nested query of (SELECT partno from publicview) for each row in table_1. Since the values in partno will always be found (as they're actually coming from table_1... not publicview!) your NOT IN produces rows containing the same partno you're testing, and therefore the NOT IN clause fails.

I hope that makes sense,

Kevin Fries
(kpf_at_landacorp.com)

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink