ORACLE: create view (aaaaagrr!)

Anon

please!

look at this:

create or replace view testvw as
select svc_key,srv_id FROM STAGE_SVCBRK_1

works ok,

create or replace view testvw as
select svc_key,srv_id FROM STAGE_SVCBRK_1 order by srv_id

returns ORA-00933!

and

create or replace view testvw as
(
select svc_key,srv_id FROM STAGE_SVCBRK_1 order by srv_id
)

returns ORA-00907!

WHY!?! i cant help... (for hours...)

thank you in advance,

robert

Anon

RTFM, as they say.

ORA-00933: SQL command not properly ended

Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY
cannot be used to create an ordered view or to insert in a certain order. Also, an improper SQL ending occurs if IN clause is used with only one argument (IN(X), for example). An IN
clause must have two or more arguments.

Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a
view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete
these spaces.

ORA-00907: missing right parenthesis

Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.

Action: Correct the syntax and retry the statement.

mikeq

Hi,

You can't use an Order by clause on a view.

A view is just like a predefined query, it doesn't actually get data until you do a select from it.

So you can do this

create or replace view testvw as
select svc_key,srv_id FROM STAGE_SVCBRK_1

then

select * from testvw
order by srv_id

this will order the data when you retrieve it.

Mike

Anon

When I log onto Oracle as scott/tiger
and issue the statement:

CREATE VIEW myview
AS
SELECT * FROM emp
ORDER BY ename;

It works and, in fact, shows the data in
increasing ename order. No problem. I am using Oracle 8i v.8.1.6.0.0. So, it seems to me that this version of oracle does somewhat support the ORDER BY clause in the SELECT statement of the CREATE VIEW statement.

However, when I issue the statement:

CREATE VIEW myview
AS SELECT * FROM emp
WHERE deptno = 10
ORDER BY ename
WITH CHECK OPTION;

Then I receive the same message as Robert.
It makes sense to me to allow a view to provide an ordering of the data to the user so that the user does not have to issue the order by clause.