Printable View
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 .
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.Quote:
Originally posted by Woo
create view exam
as
select ename,sal
from emp
order by dept
[Edited by D Maverick on 10-31-2001 at 07:57 AM]
Order by does not work in subqueries in 8. In 8i it works fine.
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:
[Edited by Raminder on 10-31-2001 at 10:51 AM]Quote:
TIP
Named views in Oracle 8i still do not support use of the order by clause.