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

"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.
>

it's strange how many problems people seem with oracle that do not have any related error messages issued.

or is Pro*C issuing a message the actually reads "doesn't like the ORDER BY clause in the subselect"

Pro*C does do some strange things, but i've not seen an error message like this before.

there may be some "compatibility" flag or some such that you can turn on. perhaps enabling or disabling semantic checking will allow it to run.

you could search metalink for a similar problem report, or open a technical assistance request with oracle support.

not all SQL constructs are supported in Pro*C or PL/SQL. these products tend to trail somewhat in their support of newer features. prior to 8i, the use of the ORDER BY clause was restricted to the outermost SELECT of nested queries. the ORDER BY could not be used as part of an inline view, or as part of a view definition.

how important is it that you get this exact statement to precompile?

if oracle can't recommend any switches or flags to use, or can't find any patches or later releases that address the problem, then you're probably going to be looking for a workaround...

since SQL now supports the ORDER BY in an inline view (which is really you've got here, not a subselect) you might try creating a view defintion that contains the order by, and then referencing the new "order by" view in your Pro*C statement, in effect, moving the ORDER BY from your SQL statement into a view definition:

CREATE VIEW ORDETAIL_ORDBY_WSKUSKUSKUID AS SELECT ORDER_IDBASE,ORDER_IDSUB,LINE,WSKUSKUYSKUID FROM ORDETAIL ORDER BY WSKUSKUSKUID and replace:

> from ordetail
> order by wskuskuskuid

with:

                                from ORDETAIL_ORDBY_WSKUSKUSKUID

if that doesn't work, another alternative may be to use a dynamic SQL call, which should get you around the precompiler syntax checks.

otherwise, you could redesign this statement and solve this problem like developers still do with Oracle 8, which still has the ORDER BY restrictions

HTH Received on Sun Jun 03 2001 - 01:16:51 CDT