If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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:

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 ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?