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