Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using 'IN' in a query

Re: using 'IN' in a query

From: Davide Pulver <david.pulver_at_profidatagroup.com>
Date: Tue, 12 Mar 2002 18:05:33 GMT
Message-ID: <Nrrj8.65$Am3.5013@nreader2.kpnqwest.net>


try the follow statement:

SELECT a, b,c,d FROM v_x WHERE (a,b) IN
(SELECT a,b FROM v_x WHERE rownum <= 3 MINUS SELECT a,b FROM v_x WHERE rownum < 2);

"ayrobins" <anthony_at_lumos.com> schrieb im Newsbeitrag news:forj8.1283$2%3.168346_at_dca1-nnrp2.news.digex.net...
> Ok i have the following schema:
> drop table x;
> create table x
> (
> a number, b number, c varchar(10), d blob,
> CONSTRAINT pk_x PRIMARY KEY(a, b)
> );
>
> insert into x (a, b, c) values (5, 6, 'a');
> insert into x (a, b, c) values (3, 2, 'b');
> insert into x (a, b, c) values (3, 1, 'c');
> insert into x (a, b, c) values (8, 7, 'd');
>
> create or replace view v_x as select a, b, c, d from x order by a, b;
>
> I want to do something like:
> SELECT a, b,c,d FROM v_x WHERE a,b IN
> (SELECT a,b FROM v_x WHERE rownum <= 3 MINUS SELECT a,b FROM v_x WHERE
> rownum < 2);
>
> But i get an error: "invalid relational operator"
>
> I understand why the error occurs. But how do i do this type of query?
I
> just want to get all rows where the Primary
> key matches the nested query. If the primary key is one column everything
> works fine (the IN operator works great with 1 column) but if the primary
> key is composite, i don't know how to form the query.
>
> Note, i could have just executed the nested query by itself, i.e.
> SELECT a,b,c,d FROM v_x WHERE rownum <= 3 MINUS SELECT a,b,c,d FROM v_x
> WHERE rownum < 2
> but MINUS does not work with BLOBS.
>
> I hope this is clear.
> thanx in advance
> anthony
>
>
Received on Tue Mar 12 2002 - 12:05:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US