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

yep. the message indicates that the pro*c syntax check of the statement is

interestingly, the error message reminds me of another "trick" that i sometimes use. when i want an ascending sort, i will sometimes use a "group by".

for example, an "order by" is not allowed (in 8.0.6) in an insert statement, for example, the following is invalid:

insert into new_table
(col1, col2, col3)
select x.col1, x.col2, x.col3
from old_table x
order by x.col1

but the following statement achieves the desired result:

insert into new_table
(col1, col2, col3)
select x.col1, x.col2, x.col3
from old_table x
group by x.col1, x.col2, x.col3

(note that the "sorted order" is achieved as a side-affect of a sort performed by oracle when it processes the "group by". that is, oracle does not guarantee that rows will be ordered, but oracle does return them in order.)

you could try replacing "order by" with a "group by" clause.

i've not tried this as part of an inline view. we needed a query that would return "the next N rows" as well as "the first N rows", so we implemented a different technique.

"Zoran Marjanski" <zoranm_at_sympatico.ca> wrote in message news:ADtS6.14925$S2.1243969_at_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
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
> 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 - 13:58:24 CDT