IBM's manual is correct, but a VIEW cannot imply order.
"VIEW cannot imply order" is one of the tenets of relational database. Order is never implied in a TABLE or a VIEW. Order is always dictated by the consumer of the SELECT. The database engine does its best to optimally produce the requested order from TABLEs and VIEWs using INDEXs.
I struggled with this, too. I wanted to have a view always produce a result set in a given order. I experimented with the same order by in a CTE you have. It never worked. As it happens, it shouldn't work.
Most database engines will produce a result set ordered by primary key if there is no ORDER BY in the SELECT. They don't have to. It just happens to be the most optimal way of producing the list. A VIEW is just a stored SELECT statement, but it must be a SELECT statement with no ORDER BY clause. Order is always determined by the final SELECT statement in the chain of SELECTs.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
Sent: Wednesday, June 05, 2013 10:47 AM
To: Midrange Systems Technical Discussion
Subject: RE: Create view using CTE as to have an ORDER BY
Then is IBM's manual incorrect? (GASP!!)
According to the V7R1 version of the IBM i:DB2 for I SQL Reference, at pages 974-975, it states:
"The ORDER BY and FETCH FIRST clauses may not be specified except within a common-table-expression."
It does not state that the ORDER BY will not be honored. My expectation was that the CTE would be constructed in the proper sequence and the subsequent select over the CTE would return the data in the proper sequence.
Steve Needles
Northland Insurance - A Travelers Company Northland Architecture
385 Washington Street, SB03N
St. Paul, MN 55102
Tel: 651-310-4203
sneedles@xxxxxxxxxxxxx
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: Wednesday, June 05, 2013 10:14 AM
To: Midrange Systems Technical Discussion
Subject: Re: Create view using CTE as to have an ORDER BY
You've done nothing wrong ORDER BY simply isn't allowed/honored in a view.
Your ORDER BY will have to be in your code.
Thanks,
Tommy Holden
From: "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 06/05/2013 10:12 AM
Subject: Create view using CTE as to have an ORDER BY
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Hey all.
I trying to create an SQL View that will use a CTE in order to use the ORDER BY clause (as suggested by the IBM i: DB2 for i SQL Reference manual on page 974-975). I am on V7R1.
CREATE VIEW needles.VIEW (Fld1 , Fld2, Fld3, Fld4) AS with interim as (SELECT Fld1, Fld2, Fld3, Fld4 FROM needles.TABLE WHERE Fld4 = 'Y'
ORDER BY Fld3, Fld2)
select * from interim
This will create the view, but the ORDER BY doesn't seem to take. Using DBU to look at needles.VIEW, I find that the rows are not in Fld3, Fld2 order.
What have I done wrong?
Steve Needles
St. Paul, MN
________________________________
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee.
Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
--
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.
--
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.
==============================================================================
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
--
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.
midrange.com
