Re: sql - create view and order by

[MEovino@xxxxxxxxxxxxxxxxx]

Cool, thanks!

Mike E.



                                                                                
                                      
                    "Smith, Dave"                                               
                                      
                    <DSmith@xxxxxxxxx>          To:     
<midrange-l@xxxxxxxxxxxx>                                     
                    Sent by:                    cc:                             
                                      
                    midrange-l-bounces@xx       Subject:     sql - create view 
and order by                           
                    drange.com                                                  
                                      
                                                                                
                                      
                                                                                
                                      
                    03/28/2003 08:25 AM                                         
                                      
                    Please respond to                                           
                                      
                    Midrange Systems                                            
                                      
                    Technical Discussion                                        
                                      
                                                                                
                                      
                                                                                
                                      




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.


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.