RE: SQL query needing Oracle cast

I am using Oracle 8.0.5

Christophe

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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:Christophe.Schockaert_at_spacebel.be
> >
> > 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: jkstill_at_cybcon.com
>
> 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: ListGuru_at_fatcity.com (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: Christophe Schockaert
  INET: Christophe.Schockaert_at_spacebel.be

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: ListGuru_at_fatcity.com (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).