Re: Help! with PL/SQL

... I do not believe you can use 'order by' in subqueries. I'm not sure whether this is a limitation that can be lifted in future versions. You may want to try other pl/sql structs or cursors within cursors!? :-)

Cosmin

In article <8nk1q7$hsb$1_at_nnrp1.deja.com>,   gdas_at_my-deja.com wrote:
> This one is really frustrating me... The following cursor declaration
> is giving me an error:
>
> cursor c1(maxid number, account number) is select feature_id, freq,
> feature, type, transform_id, unique_id, subject_id, account_id from
> (select a.feature_id, a.freq, substr(a.feature, 0, 500) as feature,
> a.type, a.transform_id, a.unique_id, a.subject_id,
> b.account_id from feature a, datastage b where a.unique_id=b.unique_id
> and b.account_id = account and a.feature_id > maxid order by
> a.feature_id ) where rownum <= 500;
>
> I'm trying to get only the first 500 rows, so I'm sorting inside the
> inline view and then applying a rownum constraint in the outer select.
> The sql itself works just fine if I run it through sql plus (and of
> course filling in some literals for the input parameters.)
>
> When trying to complile the procedure however, I keep getting this
> error :
>
> 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 ||
>
> For some reason it does not like the order by clause inside of the
> inline view definition ("order by a.feature_id "). If I remove that
> order by clause the procedure will compile successfully.
>
> I'm running 8.1.5 on NT. I can't see what I am doing wrong here, I'm
> hoping perhaps another pair of eyes looking at this might be able to
> spot something obviously. But the fact that the sql itself runs fine
> after inserting literals for the input parameters has me thinking I
> might have stumbled on a either a bug or some kind of limitation of
> PL/SQL...
>
> Thanks for any tips.
> Gavin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 18 2000 - 14:30:10 CDT