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

From: Zoran Marjanski <zoranm_at_sympatico.ca>
Date: Sun, 03 Jun 2001 16:31:28 GMT
Message-ID: <ADtS6.14925$S2.1243969@newsread2.prod.itd.earthlink.net>

Spencer, thanks for the tips. Sorry for not including text of the error msg. It is below.

We ended up solving the problem by adding an index to the ORDETAIL table that would ensure the index be used by Oracle so that the rownum could be effective.
Pro*C/C++: Release 8.1.6.0.0 - Production on Sun Jun 3 12:25:20 2001

System default option values taken from: /u01/app/oracle/product/8.1.6/precomp/admin/pcscfg.cfg

Syntax error at line 96, column 30, file /wms/work/zmarjans/csiint/src/precomp/osf51/oescrn_dbafSelectOrDetail.pcc:

Error at line 96, column 30 in file
/wms/work/zmarjans/csiint/src/precomp/osf51/

oescrn_dbafSelectOrDetail.pcc

                       order by wskuskuskuid

.............................1

PCC-S-02201, Encountered the symbol "by" when expecting one of the following:

, ) union, connect, group, having, intersect, minus, start,

where, with,

The symbol "group," was substituted for "by" to continue.

Zoran.

"Spencer" <spencerp_at_swbell.net> wrote in message news:GBkS6.222$Z42.11659_at_nnrp1.sbc.net...
> "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 - 11:31:28 CDT

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