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



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.


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.