order by clause in a view definition subquery

[Woo]

Hi, all of my teachers !

In a oracle SQL study guide it says " Do not use order by clause in subquery". But as you see this code snippet below

create view exam
as
select ename,sal
from emp
order by dept

This code I wrote didn't cause error at all ! Then , now I wonder why the text book recommend not to use order by clause in a subquery in a view.

Can anyone explain me why?

Thank in advance .

[D Maverick]

Originally posted by Woo
create view exam
as
select ename,sal
from emp
order by dept

Hi, i dont see any subquery in that statement. The SELECT part of that statement is not actually a subquery. A subquery is a query within a query.


[Edited by D Maverick on 10-31-2001 at 07:57 AM]

[julian]

Order by does not work in subqueries in 8. In 8i it works fine.

[Raminder]

The problem is, 'Order By' clause could'nt be used in View Creation statements in Oracle 8. I have also encountered the same mistake in some documents for Oracle 8i.
Since, by definition, a table is an unordered set of rows, it (order by) still cannot be used in certain situations; like create table statement with 'AS SELECT FROM ......... another_table' and in subqueries. However, in queries containing subqueries, order by can be used in the outer-most query.

In Page 95 of Oracle Press Book Oracle Certified Professional DBA Certification Exam Guide by Jason S. Couchman and Ulrike Schwinn says:

TIP
Named views in Oracle 8i still do not support use of the order by clause.

[Edited by Raminder on 10-31-2001 at 10:51 AM]