Re: Ordered Inline View

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 31 May 2000 11:14:33 -0400
Message-Id: <10514.107308@fatcity.com>

Sorry,

The text was wrong. Here is the correct one:

SELECT cycle_date_time FROM (

    SELECT /*+ FIRST_ROWS */ cycle_date_time

        FROM CYCLE
        WHERE cycle_date_time < SYSDATE
          AND gauge_id = 2
          AND model_id = 16
        ORDER BY cycle_date_time ASC)

    WHERE ROWNUM < 2;

and it really works.

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- From: "Killough, Mike" <mkillough_at_griffithlabs.com> To: "'Igor Neyman'" <ineyman_at_perceptron.com>; <oracledba_at_quickdoc.co.uk>; <ORACLE-L_at_fatcity.com> Sent: Wednesday, May 31, 2000 11:10 AM Subject: RE: Ordered Inline View

> Are you SURE that this sql stmt works? It looks like an extra ")" to me...
>
> > -----Original Message-----
> > From: Igor Neyman [SMTP:ineyman_at_perceptron.com]
> > Sent: Wednesday, May 31, 2000 9:15 AM
> > To: oracledba_at_quickdoc.co.uk; ORACLE-L_at_fatcity.com
> > Subject: Ordered Inline View
> >
> > I have the followingproblem, when using ordered inline view.
> > When I execute a single sql-statement (from SQL*PLUS):
> >
> > SELECT /*+ FIRST_ROWS */ cycle_date_time
> > FROM CYCLE
> > WHERE cycle_date_time < SYSDATE
> > AND gauge_id = 2
> > AND model_id = 16
> > ORDER BY cycle_date_time ASC)
> > WHERE ROWNUM < 2;
> >
> > it works fine.
> > But, when I put this statement into PL/SQL block, I am getting
> > an error:
> > SQL> declare lDate date;
> > 2 begin
> > 3 SELECT cycle_date_time INTO lDate
> > 4 FROM(
> > 5 SELECT /*+ FIRST_ROWS */ cycle_date_time
> > 6 FROM CYCLE
> > 7 WHERE cycle_date_time < SYSDATE
> > 8 AND gauge_id = 2
> > 9 AND model_id = 16
> > 10 ORDER BY cycle_date_time ASC)
> > 11 WHERE ROWNUM < 2;
> > 12 end;
> > 13 /
> > ORDER BY cycle_date_time ASC)
> > *
> > ERROR at line 10:
> > ORA-06550: line 10, column 3:
> > 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 ||
> > The symbol ")" was substituted for "ORDER" to continue.
> > ORA-06550: line 10, column 31:
> > PLS-00103: Encountered the symbol ")" when expecting one of the
> > following:
> > , ; for
> >
> > SQL>
> >
> > It looks, like PL/SQL engine does not like ordered inline views
> > (though they work fine in SQL).
> > Do you know, if this is a known bug?
> > Or, there is any workaround?
> >
> > TIA
> >
> > Igor Neyman, OCP DBA
> > Perceptron, Inc.
> > (734)414-4627
Received on Wed May 31 2000 - 10:14:33 CDT