views & order by

[ocpdude]

I am trying to create a view using a select statment
which has order by clause. I keep getting this message
ORA-00933: SQL command not properly ended.
if I take out order by from the select it works fine.
Any Idea on how to do create views with order by??
thanks in advance

[ssu4716]

Please paste the query including the table structures.

Thanks,

Syed

[ocpdude]

Hi, here is the statment
create view test_v as
select x, y from xy_table
order by x,y

[Edited by ocpdude on 11-08-2000 at 12:58 PM]

[ssu4716]

There is nothing wrong with your create view statement.
Where are you trying to run it, on SQLPLUS?

If so the paste a snapshot of:

SQL> desc xy_table
SQL> create view test_v as
select x, y from xy_table
order by x,y;

including the output you get.

Thanks.

Syed

[ocpdude]

Hi,
yes I am using sqlplus oracle8.0.5

SQL> ed
Wrote file afiedt.buf

1 create or replace view test_v as
2 select obl_num from obl
3* order by obl_num
SQL> /
order by obl_num
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

[mber]

Sorr, in the old version of 8.0.5, U can not create view by using "order by" statement. But the current version support it.

So, create the view first, and then sort it when select the data from the view.

Take care

[ocpdude]

thanks