× 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.



Oops! The CREATE VIEW should have been

CREATE VIEW VERN/xxview ASwith xx as (SELECT distinct SCstDATE, SCstTIME FROM vern/ptf order by scstdate desc) select scstdate, scsttime from xx

Vern

At 02:57 PM 7/24/2004, you wrote:
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 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.