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

["Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>]

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.