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

On 05 Jun 2013 08:10, Needles,Stephen J wrote:

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?

The Common Table Expression [IIRC, any /derived/ table] can effect ordering, but unless another subquery expression takes advantage of that ordering [e.g. FETCH FIRST clause], the actual data in the temporary table that is created need not be ordered. A query rewrite could effectively eliminate the ORDER BY clause, or even eliminate the CTE entirely [which is likely in the given scenario]. In the given scenario the ORDER BY likely has no effect except for a possible optimization for use of an index [partialy] on (Fld4,Fld3,Fld2); but again, the CTE is probably optimized out of the query per the effects of /query rewrite/ activity.