Re: "Any" Syntax?

It looks wrong. If you use =any, it returns 1 and 2, what you would expect. If you use != ALL, then it returns 3.
I tested under 7.3.4

--
Terry Dykstra
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Doug Cowles <dcowles_at_us.ibm.com> wrote in message news:3891C176.5444928E_at_us.ibm.com...
> It may be valid syntax but it doesn't appear to work. I did this on
Oracle
> 8.1.5
> running AIX 4.2.3
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> create table a (a number);
>
> Table created.
> SQL> insert into a values (1);
>
> 1 row created.
>
> SQL> insert into a values (2);
>
> 1 row created.
>
> SQL> insert into a values (3);
>
> 1 row created.
>
> SQL> create table b (b NUMBER);
>
> Table created.
>
> SQL> insert into b values (1);
>
> 1 row created.
>
> SQL> insert into b values (2);
>
> 1 row created.
>
> SQL> select a from a where a != any (select b from b);
>
> A
> ----------
> 1
> 2
> 3
>
> SQL>
>
> Terry Dykstra wrote:
>
> > It's definitely valid syntax. Check your Server SQL reference under the
> > Comparison Operators.
> >
> > --
> > Terry Dykstra
> > Canadian Forest Oil Ltd.
> > Check out Sybase Developer's Network: http://www.sybase.com/sdn
> >
> > Doug Cowles <dcowles_at_us.ibm.com> wrote in message
> > news:3890AE0F.96FF2E9F_at_us.ibm.com...
> > > A developer gave me a query to tune up today using a syntax I'm not
> > > familiar with.
> > > On top of that, it doesn't appear to work right either. However, it
> > > produces no error
> > > message. Given 2 tables like
> > > create table A( a number);
> > > create table B( b number), with some values in them, the query did
> > > something like this
> > > (among other things)..
> > > select a from A
> > > where a != any
> > > (select b from B);
> > > It would appear to be an attempt to strip out values that appear in B,
> > > sort of a substitute
> > > for "not in". But as I said, it actually doesn't strip out anything,
> > > but doesn't generate
> > > any errors.
> > >
> > > So, is this some ANSI thing that compiles but doesn't work? Or maybe
> > > it's intended for
> > > another use and isn't being implemented right in this example?
> > >
> > > Any comments appreciated,
> > > Thanks,
> > > Dc.
> > >
>
>
>
Received on Fri Jan 28 2000 - 12:32:30 CST