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

[Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx]

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