Christophe,
I'm not sure why you are trying to use CAST and MULTICAST
as these are for use with object oriented features of Oracle.
Below is an example of Top N queries. The 8i version is somewhat
simpler, as an 'ORDER BY' is allowed in the subquery.
This example also has one less level of subquery. Yours appears
to be more complex than necessary.
HTH
Jared
----------------------
drop table limit_tab;
create table limit_tab ( x number );
insert into limit_tab values(0);
insert into limit_tab values(1);
insert into limit_tab values(2);
insert into limit_tab values(3);
insert into limit_tab values(4);
insert into limit_tab values(5);
insert into limit_tab values(6);
insert into limit_tab values(7);
insert into limit_tab values(8);
insert into limit_tab values(9);
commit;
-- Oracle 8.1.x
select ilv.x
from
(
select x
from limit_tab
order by x
) ilv
where rownum <=3
/
-- Oracle 8.0.x
select x
from limit_tab lt
where 3 > (
select count(*)
from limit_tab
where x < lt.x
)
order by x
/
----------------------
On Tuesday 08 May 2001 06:25, Christophe Schockaert wrote:
> I am using Oracle 8.0.5
>
> Christophe
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jared Still
> > Sent: lundi 7 mai 2001 21:17
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: SQL query needing Oracle cast
> >
> >
> >
> > Chris, there are other ways to do this, but they are version dependant.
> >
> > Which version of Oracle are you using?
> >
> > Jared
> >
> > On Monday 07 May 2001 14:07, Christophe Schockaert wrote:
> > > Hi all,
> > >
> > > I need to get the top N values from a table.
> > > It's quite easy to formulate in english, but rather interesting
> >
> > to write in
> >
> > > SQL.
> > > I found a solution at
> >
> > http://www.4guysfromrolla.com/webtech/110498-1.shtml
> >
> > > The example has a table ItemCost containing ItemNumberID (int) and Cost
> > > (money).
> > > The query is:
> > >
> > > SELECT rank, ItemNumberID, Cost
> > > FROM (SELECT T1.ItemNumberID, T1.Cost,
> > > (SELECT COUNT(DISTINCT T2.Cost) FROM ItemCost T2
> > > WHERE T1.Cost <= T2.Cost) AS rank
> > > FROM ItemCost T1) AS X
> > > WHERE rank<N ORDER BY rank
> > >
> > > The problem is that Oracle refuses the (select COUNT ...) in the second
> > > SELECT clause.
> > > It seems that I have to use CAST but I'm getting lost with the syntax.
> > > According to the documentation, I have to use CAST(MULTISET ...) if the
> > > query will result in several rows. It is also said that scalar
> >
> > subqueries
> >
> > > as argument of the CAST operator are not valid in Oracle8. Do I have to
> > > consider SELECT COUNT as a scalar subquery ? It is not a
> >
> > multi-rows query
> >
> > > anyway.
> > > However, whether I use CAST, CAST(MULTISET) or just the example above,
> > > I get an error from Oracle.
> > >
> > > Does anybody know how I can translate the example to Oracle, or
> >
> > how I can
> >
> > > write a query in the Oracle SQL syntax which will give me the
> >
> > result I want
> >
> > > ?
> > >
> > >
> > > Thanks in advance,
> > >
> > > Christophe
> > >
> > > >>>>---------------> mailto:[EMAIL PROTECTED]
> > >
> > > Once it's perfectly aimed, the flying arrow goes straight to its
> > > target. Thus, don't worry when things go right.
> > > There will be enough time to worry about if they go wrong.
> > > Then, it's time to fire a new arrow towards another direction.
> > > Don't sink. Adapt yourself ! The archer has to shoot accurately and
> > > quickly.
> > > [Words of Erenthar, the bowman ranger] <---------------<<<<
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jared Still
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
