Re: ORDER BY in sub-select doesn't compile in Pro*C 8.1.6 - why not?

Zoran can't use that because it doesn't meet the requirement to return the first 9 rows from an ordered set. that is, rownum is assigned BEFORE the order by.

Pro*C and PL/SQL tend to lag behind in new features, the use of order by on an inline view is just one example.

you have a couple of choices:

• dynamic SQL (to get around the Pro*C syntax check)
• rewrite the query (to avoid the use of the inline order by)

"Chris Wiss" <chris_at_purecarbonnospam.com> wrote in message news:9fj9co$1ku1$1_at_msunews.cl.msu.edu...
> This construct seems unnecessarily complex. Why not simply use the
> following:
>
> EXEC SQL select order_idbase, order_idsub, line, wskuskuskuid
> into :order_idbase, :order_idsub, :line
> from ordetail
> where rownum < 10
> order by wskuskuskuid
>
> Chris Weiss
>
> "Zoran Marjanski" <zoranm_at_sympatico.ca> wrote in message
> news:wYeS6.13420$S2.1037996_at_newsread2.prod.itd.earthlink.net...
> > We have Oracle 8i (8.1.6) and Pro*C 8.1.6 pre-compiler and the following
 SQL
> > is NOT pre-compiling. Pro*C doesn't like the ORDER BY clause in the
> > sub-select. We need the order by in the sub-select to make the rownum
 clause
> > in the outer select do it's job.
> >
> > EXEC SQL select order_idbase, order_idsub, line, wskuskuskuid
> > into :order_idbase, :order_idsub, :line
> > from (select order_idbase, order_idsub, line,
> > wskuskuskuid
> > from ordetail
> > order by wskuskuskuid
> > )
> > where rownum < 10
> > ;
> > The query however is supported by Oracle 8i and executes just fine in
> > SQL*Plus.
> >
> > Does anyone know, how to get the pre-compiler to like this syntax with
 the
> > order by in the sub-select?
> >
> > Thanks, Zoran.
> >
> >
> >
>
>
>
Received on Sat Jun 09 2001 - 08:53:41 CDT