Re: ORDER BY in View's subquery

From: <tncc>
Date: Wed, 19 Jan 2005 15:54:00 +0800
Message-ID: <41ee1218$1_2@rain.i-cable.com>

I cannot find any info mentioning there was a change from 9iR1 to 9iR2. Instead, in 9iR1 SQL Reference for CREATE VIEW, the ONLY restrictions listed are as follows. There is nothing related to ORDER BY clause. So, I just think even 9iR1 is allowing it in view's subquery.

In that case, I just doubt why there is such a restriction mentioned in the student's guide. I is not going to be just a typo.

** QUOTE 9iR1 SQL Reference CREATE VIEW ********************************

.....
Restrictions on the view subquery:

*The view subquery cannot select the CURRVAL or NEXTVAL pseudocolumns.

*If the view subquery selects the ROWID, ROWNUM, or LEVEL pseudocolumns, those columns must have aliases in the view subquery.

*If the view subquery uses an asterisk (*) to select all columns of a table, and you later add new columns to the table, the view will not contain those columns until you re-create the view by issuing a CREATE OR REPLACE VIEW statement.

*For object views, the number of elements in the view subquery select list must be the same as the number of top-level attributes for the object type. The datatype of each of the selecting elements must be the same as the corresponding top-level attribute.

*You cannot specify the SAMPLE clause.

---

"Sybrand Bakker" <gooiditweg_at_sybrandb.verwijderdit.demon.nl> ¦b¶l¥ó news:iq0su0hvsl3ubjqbduvbfl86hjl3pi5bt1_at_4ax.com ¤¤¼¶¼g...
> On Wed, 19 Jan 2005 13:04:29 +0800, <tncc> wrote:
>
> >The student guide for Introduction to Oracle9i: SQL mentioned that "the
> >subquery that defines the view cannot contain an ORDER BY clause" (chap
> >11-8). But I tested this with 9iR2 and the ORDER BY is allowed?! Is
this
> >just a difference between 9iR1 and 9iR2? Or is there something I am
missing
> >and a view's query really cannot have an ORDER BY ?
> >
> >Thx for any advice
> >
>
> You may want to check the release notes for 9ir2. If it is a new
> feature, it will have been documented.
> IMO, they shouldn't have done this. A set is by design unordered.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Jan 19 2005 - 01:54:00 CST