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