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

This makes me wonder what is the point of ORDER BY in a CTE - unless the optimizer can use it. As has been implied, the way to get the order is to use ORDER BY in a SELECT over the view.

----- Original Message -----
DRAT!! It was worth a try. Thanks all.

If anyone comes up with a solution, please advise!!

Thanks again!

Steve Needles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, June 05, 2013 10:53 AM
To: Midrange Systems Technical Discussion
Subject: RE: Create view using CTE as to have an ORDER BY

The order by will be respected within the CTE. If your nesting multiple CTE's to group then calculate then ... that's ok. Just do not expect the final result to come out in any particular order.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>,

Date: 06/05/2013 11:47 AM
Subject: RE: Create view using CTE as to have an ORDER BY
Sent by: midrange-l-bounces@xxxxxxxxxxxx

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.