RE: Create view using CTE as to have an ORDER BY -- MIDRANGE-L

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.