thanx alot. it works!
"Davide Pulver" <david.pulver_at_profidatagroup.com> wrote in message
news:Nrrj8.65$Am3.5013_at_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:52:08 CST
