Re: order by view
"malcolm.iiug" <malcolm.iiug@btopenworld.com> wrote in message news:<bhcvds$akp$1@terabinaries.xmission.com>...
> I think the restriction is likely to come about due to the problems of
> resolution when the view contains an order by which is in disagreement with
> the order by clause in the select statement which uses the view.
> I can only assume that the more inefficient way that Oracle does things is
> the reason why Oracle allows this. What does the SQL standard say?
>
> Malcolm
The SQL standard (SQL-99) doesn't allow ORDER BY in the CREATE VIEW
statement.
To find out if an SQL statement is standard compliant or not, you can
use the SQL Validator, http://developer.mimer.com/validator/.
In this case you'll get the following result:
create view viewname
as select a,b
from table1
order by b
^----syntax error: order
correction: GROUP
/Jarl
> ----- Original Message -----
> From: "rkusenet" <rkusenet@sympatico.ca>
> To: <informix-list@iiug.org>
> Sent: Tuesday, August 12, 2003 9:04 PM
> Subject: order by view
>
>
> > It seems even in Informix 9.4 we can not use order by clause
> > in a view
> >
> > create view viewname> > as select a,b
> > from table1
> > order by b ;
> >
> > will result in syntax error.
> >
> > SQl Server 2000 also does not allow this. However Oracle
> > allows.
> >
> > We recenetly had to change the definition of a view and would
> > have loved to have order by clause in the view definition. Now
> > we are forced to change the application code for proper ordering.
> >
> > why is this restriction??
> >
> >
> >
>
> sending to informix-list