sql - create view and order by

["Smith, Dave" <DSmith@xxxxxxxxx>]

Mike:

You are correct, no order by clause when creating a view (Not even in V5R2).   
I think your best bet if you need this around all the time is create the view 
without the order by.  Then make sure you have an indexes set up over the PF(s) 
that your view is using.  Indexes are usually based on your WHERE clause.

A good way to determine what indexes to create is to run the select portion 
with the order by under iseries Navigator using VisualExplain Run and Explain.  
It will tell you exactly what indexes to set up.

Then do what you said in option 2 below: "...then use SQLRPGLE and have the 
SELECT order the view as I need it."  It should be quick at that point.

David Smith
Tri-State Hospital Supply Corp.
IT Consultant
dsmith@xxxxxxxxx
517.546.5400



message: 9
date: Thu, 27 Mar 2003 22:37:43 -0500
from: MEovino@xxxxxxxxxxxxxxxxx
subject: sql - create view and order by


It looks like there's no way to put an ORDER BY clause in a CREATE VIEW
statement.  I'm trying

create view mikee/osp1 as select * from mikee/osp001 inner join
mikee/osp002 on ohtid = odtid and ohseq = odseq order by oheda8

and getting

Keyword ORDER not expected. Valid tokens: <END-OF-STATEMENT>.

Am I correct in that assumption?  And if so, does anyone have a nifty way
to get around this?  I was hoping to create a view that joins two files and
orders them the way I need them.

If I can't do this, which of the following would provide better
performance:

1.  Use SQLRPGLE and have the SELECT join and order the files as I need
them.
2.  Create a view that joins the files, then use SQLRPGLE and have the
SELECT order the view as I need it.

Mike E.