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

Home -> Community -> Usenet -> c.d.o.server -> sql question with ROWNUM

sql question with ROWNUM

From: kush <kdhakal_at_eline.com>
Date: Fri, 09 Feb 2001 22:47:58 GMT
Message-ID: <ys_g6.13171$1%2.667705@sjc-read.news.verio.net>

hi! I want to get only top ten records after order by clause is met, query looks like this:
SELECT * FROM (select distinct p.prodid,pr.prodID as reviewed,p.title,p.imagefile,p.priority,p.price,b.qty_sold

    from bestsellers_twoweeks b, product p, products_reviewed pr     where b.selectionid = 3
    and b.prodid=p.prodid
    and pr.prodID (+)= p.prodID
    order by b.qty_sold desc) WHERE ROWNUM <= 10; It gave me results as I expected when I tried to run it in sql editor TOAD. But the problem is I need to use it inside a package with ref cursor and when I tried to compile it it gives me error at line number where the order by clause is:
open r_cursor for

       select * from (select distinct p.prodid, pr.prodID as reviewed, p.title, p.imagefile, p.priority, p.price,b.qty_sold

    from bestsellers_twoweeks b, product p, products_reviewed pr     where b.selectionid between selbottom and seltop     and b.prodid=p.prodid
    and pr.prodID (+)= p.prodID
    order by b.qty_sold desc) where rownum <=10;

PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:

   . ( ) * @ % & - + / mod rem with an exponent (**) and or    group having intersect minus start union where connect ||

Could anyone tell me why I can run this query in sql editor and can't compile same inside package. I am using Oracle 8.1.5. Thanks. Received on Fri Feb 09 2001 - 16:47:58 CST

Original text of this message

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