|
Oops! The CREATE VIEW should have been
Vern
With you, Joel
Even at V5R3, views do not have an ORDER BY clause. I just realized you did not say that, rather, that you need an ORDER BY in your SELECT over a view, to get sorted results.
There is some confusion, I think, because a common table expression (CTE) can have an ORDER BY since V5R2, and CTEs can be used in a view. So I tried something:
CREATE VIEW VERN/xxview AS
If you run the select from the CTE statement
SELECT distinct SCstDATE, SCstTIME FROM vern/ptf order by scstdate desc
by itself, it'll be in descending order by date. If you run
SELECT * FROM VERN/XXVIEW
it is NOT in that order. Maybe this is a bug.
If you run the whole CTE
with xx as (SELECT distinct SCstDATE, SCstTIME FROM vern/ptf order by scstdate desc) select scstdate, scsttime from xx
by itself, the result is NOT sorted. Again, is this a bug? I might ask IBM.
So, the returned order of a SELECT is unpredictable, and even more so when you add the possibility of SMP and parallel access methods. The SQL manuals say that the only way to guarantee a certain order is to include an ORDER BY, and it appears that temporary results don't count. This goes along with your statement about needing an ORDER BY in your SELECT.
Hmm!
Vern
At 12:06 PM 7/23/2004, you wrote:On Fri, 2004-07-23 at 20:02, midrange-l-request@xxxxxxxxxxxx wrote:
> message: 3
> date: Fri, 23 Jul 2004 20:37:22 +0100
> from: "Paul Tuohy" <tuohyp@xxxxxxxxxxxxx>
> subject: Views and Indexes (was Re: MIDRANGE-L Digest, Vol 3, Issue
> 1075)
>
> Hi Joel,
>
> I think the clarity needs some clarification :-)
>
> You are correct that views have columnar abilities (quite a lot of them)
> that are not available in DDS but views and indexes are two very seperate
> things.
>
> A view does NOT incorporate an index. When using a view (using SQL select)
> it is the ORDER BY CLAUSE and WHERE clauses that are used to determine which
> index is used.
>
> A logical file is not just an index. It can do sequence, selection,
> projection, union and join - just not as many options as SQL. It's one big
> advantage (as Rob pointed out) is that a logical file can define a combined
> view and index. This is of enormous value for traditional I/O. Only SQL
> defined indexes are really accessible by traditional I/O - which means you
> can't make use of all those cool features that wer defined in views (without
> using embedded SQL).
>
> Paul Tuohy
Paul,
I stand clarified :-)
I didn't say that indexes and views were the same thing: I know what capabilities a view has, but I was under the mistaken impression that because of the "where" and "order by" capability of a view that it represented the index as well. I looked it up and sure enough a view is a "virtual table" that the underlying DBMS must execute at run time. This means that it has to find its own indexes. So can you write an index over a View?
I still don't think a logical can compete with the functionality of a view, but I'll admit I did under-represent them. "Only SQL defined indexes are really accessible by traditional I/O" - what do you mean?
Joel http://www.rpgnext.com
-- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
