Re: sql question with ROWNUM

Hi Kush,

I have run into this problem before. This is a Oracle8i PL/SQL bug. The work around is to make your sql into a dynamic sql string. i.e.

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';

Ling

"kush" <kdhakal_at_eline.com> wrote in message news:ys_g6.13171$1%2.667705_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 Fri Feb 09 2001 - 22:58:20 CST