From jkstill@cybcon.com Tue, 08 May 2001 10:49:38 -0700 From: Jared Still Date: Tue, 08 May 2001 10:49:38 -0700 Subject: Re: SQL query needing Oracle cast Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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: root@fatcity.com [mailto:root@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 > > > > > 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@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@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@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: Jared Still INET: jkstill@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@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).