× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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>



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.