Re: what's here with not in select ?

On Wed, 25 Feb 1998 14:47:29 +0100, "Valent Levente" <lsoft_at_szabinet.hu> wrote:

Hi,

be aware that your column's name of table jobs is 'job_name' not 'name'! So

select * from people where name not in (select job_name from job);

might do your job.

>This test was produced on Oracle 7.3
>
>Oracle7 Workgroup Server Release 7.3.2.3.1 - Production Release
>With the distributed option
>PL/SQL Release 2.3.2.3.0 - Production
>
>
>Here is a spool file of a test, but how can it happen ?
>Ia have a table, that doesn't have a column, that I reference in the not in
>(select....) section of a query. Oracle says, that no rows selected, but I
>think, that the correct answer is 'Invalid column name', I think
>
>I was able to produce this BUG with every table and column name, the test
>names are not special.
>
>create table people( name varchar2(40) );
>Table created.
>create table job( job_name Varchar2(40) );
>Table created.
>
>insert into people values('JOE');
>1 row created.
>insert into people values('NOT JOE');
>1 row created.
>insert into people values('THIS IS JOE');
>1 row created.
>
>insert into job values('JOE IS NOT HERE');
>1 row created.
>insert into job values('AND NO OTHER PEOPLE');
>1 row created.
>
>select * from people where name not in (select name from job);
>no rows selected
>
>
>/* this is the subquery itself */
>
>select name from job;
>select name from job
> *
>ERROR at line 1:
>ORA-00904: invalid column name
>
>spool off
>
>--
>Valent Levente
>lsoft_at_szabinet.hu
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de