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

Thread: REGARDING VIEWS

  1. #1
    Join Date
    Mar 2001
    Posts
    8

    Question

    Hi,
    I would like to know whether we could use an order by clause in a view. It has been given in oracle documentation that a query that defines a view cannot contain an order by clause and for update clause. When I went through oracle 8 administrators guide it has been given that you cannot use only for update clause.
    when I used the order by clause in defining a view query I was not getting any errors.

    SQL> CREATE VIEW SALES_STAFF1 AS
    2 SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE DEPTNO=10 ORDER BY DEPTNO;

    View created.


    But when I used the order by clause when defining a view query with check option constarint I am getting the following error:

    SQL> CREATE VIEW SALES_STAFF2 AS
    2 SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE DEPTNO=10 ORDER BY DEPTNO
    3 WITH CHECK OPTION CONSTRAINT SA_STAFF_CNST;
    WITH CHECK OPTION CONSTRAINT SA_STAFF_CNST
    *
    ERROR at line 3:
    ORA-00933: SQL command not properly ended

    I would also like to know in which versions order by clause was allowed to be used when defining a view query.

    Any advice or suggestions would be of great help.
    nukavarapu
    Kumar

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The documentation clearly states that you can't have an order by clause in your view definition. I would guess that is the reason you are getting your error.

    That being said, a plain view can have an order by in its's definition. It may not be supported, but it works...

    SQL> select * from xyz;

    X Y Z
    -- -- --
    x y z
    z y x
    a b c

    SQL> create view xyz_view as select * from xyz order by x;

    View created.

    SQL> select * from xyz_view
    2 /

    X Y Z
    -- -- --
    a b c
    x y z
    z y x
    Jeff Hunter

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Depending on which release of softwre you are using and/or which release of documentation you are reading.

    ORDER BY inside a view was not allowed in releases up to (and including) 8.0.*, and it was also explicitely documented in the documentation up to 8.0 that you can't use ORDER BY in a view.

    Starting from 8i (8.1.*) ORDER BY is allowed in view definition (both in explicit and inline views). In the documentation 8.1.5 and above ORDER BY is not listed among restrictions in CREATE VIEW command, hence it is allowed. However there is no mentioning in the documentation that ORDER BY and WITH CHECK OPTION can not be used together. So I gues this could be:
    a) documentation bug
    b) software bug
    c) undocumented feature
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2001
    Posts
    51

    As regards order by clause in views, they can be used in oracle 8i version onwards.

    And as regards with check option conatraint, it can't be used with the order by clause.

    Hope this helps u.


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