-
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
-
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... http://www.plauder-smilies.de/faga1.gif
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
-
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 ;)
-
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.
