DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: order by clause in a view definition subquery

  1. #1
    Join Date
    Oct 2001
    Posts
    22

    Unhappy

    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 .

  2. #2
    Join Date
    Sep 2001
    Posts
    261
    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]
    The Maverick
    Oracle Certified DBA - 8i

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Order by does not work in subqueries in 8. In 8i it works fine.


  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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]

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width