Re: sql - create view and order by

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Rick, your response shows some of the differences between SQL techniques 
and DDS techniques.

Views are a special kind of logical file. They don't even have an access 
path - only the SELECT statement stored in the file. The SELECT has to be 
run anew every time you use the view.

That is probably why you cannot ORDER BY a view - there is no data to sort. 
This is not an AS/400 restriction, ASAIK - it is SQL standard.

There seems no reason, than theory, for not having an ORDER BY on a view, 
since it could be run as part of the use of the view. But what do I know?  <g>

That is also why you cannot create an index over a view - an index must 
have physical data to index.

If your view is fairly simple, indexes over the constiuent physical fiels 
(tables) may help. But if your view needs to build a temporary result set, 
you're out of luck, since there is no linkage from an index to a temp 
result set built from the table over on the index is based.

This is also why it is often unwise to have views based on views - it's 
better to build an intermediate table, maybe repopulating one that has an 
index over it.

Views are handy but may kill performance. Your suggestion of join logicals 
has much to say for it. Of course, there is more flexibility with views and 
SELECT statements - keys across the dials of a join, e.g.

Regards

Vern

At 08:13 AM 3/28/2003 -0500, you wrote:

> Mike,
>
> I believe you are correct. views don't allow the order by clause.
>
> Can you create an index over the view?  are you going to use this
> view/index exclusively with sql?
>
> if so, you can do your 'order by's when you use the view.  if not, why not
> just create a joined logical? (two birds, one stone)
>
> Rick
>
> ----original message------
> <snip>
> It looks like there's no way to put an ORDER BY clause in a CREATE VIEW
> statement.  I'm trying
> <snip>