Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SQL query needing Oracle cast

SQL query needing Oracle cast

From: Christophe Schockaert <Christophe.Schockaert_at_spacebel.be>
Date: Mon, 07 May 2001 13:04:03 -0700
Message-ID: <F001.002FB534.20010507130703@fatcity.com>

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: 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).
Received on Mon May 07 2001 - 15:04:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US