Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: using 'IN' in a query
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