Re: SQL question with ROWNUM

ORDER BY clause must follow
WHERE clause

Cheers
Allan

"kush" <kdhakal_at_eline.com> wrote in message news:OHVh6.13481$1%2.710868_at_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 Mon Feb 12 2001 - 16:25:02 CST