To all the good ppl on the list ...
-
Answer from Luis Rodriguez:
"your "outer" SELECT does not have an ORDER BY clause (and, of course, being a view it can't have one), so the output sequence can't be assured."
That makes sense. Thank you for pointing it out.
=========
Answer from Rob:
"The ORDER BY and FETCH FIRST clauses may not be specified except within a common-table expression.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhcview.htm "
Thanks for the explanation and the link. It was very helpful. Example 6 at the link reinforced what you wrote.
=========
Answer from John Yeung
"To run with that for a bit: There are plenty of ways to generate Excel directly on the i" .
What method would you recommend? What have you tried and which method do you favor?
=========
What I did to resolve my problem....
With the new knowledge gained from all of you, I know realize that a view can't have an order.
So I followed example 6 and used the newly created view to insert records into a physical file. The insert can have an order by clause. Then I download the physical and order is maintained.
Not a great solution, but it will work for now.
Here's the SQL ...
insert into qgpl/filePF
(f1file,f1mbr,f1mbrname,f1lib,f2file,f2mbr,f2mbrname,f2lib)
select
f1file,f1mbr,f1mbrname,f1lib,f2file,f2mbr,f2mbrname,f2lib
from qgpl/viewAB
order by coalesce (f1lib,f2lib), coalesce(f1file,f2file), coalesce(f1mbrname,f2mbrname)
midrange.com
