RE: Use SQL instead of RPG?

 
> I don't believe you can use the ORDER BY clause with a view.  At
least,
> not at V5R3.  You also can't create an index over a view.  Using a
view
> would require two SQL statements.  One for the view and a second
select
> to order the records.

[Beeson, Terry] 
You're quite right.

> If the records are to be placed into a subfile why not just open a
> cursor for the select statement and populate the subfile from the
result
> set?  Do it all in a single RPG program.

 [Beeson, Terry] 
In addition, why run the SQL in batch to feed a subfile that can only be
accessed interactively?
Am I missing something?

> 
> Rick
> 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Beeson, Terry
> > Sent: Thursday, May 18, 2006 4:23 AM
> > To: RPG programming on the AS400 / iSeries
> > Subject: RE: Use SQL instead of RPG?
> >
> > You can also do a CREATE VIEW.  This creates a logical file
> > (but the access paths aren't built until you open the file).
> > You can execute this in CL, it will be visible to your RPG,
> > and do a DROP VIEW after the call to your RPG.
> >
> > To call the SQL in CL, use the STRQMQRY as suggested by Michael.
> > When creating the QMQRY object, there is a way to enter SQL
> > statements directly.
> > Before taking option 1 as described below, press F19 first.
> > This will change the mode of entry from PROMPT to SQL,
> > allowing you to enter the SQL statements directly.
> >
> > It is very simple to create an SQL view, simply precede your
> > SQL statement with the words CREATE VIEW <name> AS
> >
> > For example:
> > CREATE VIEW MYVIEW as
> > Select part1 as part, co1 as co, div1 as div, qty1 as qty
> > >From myfile
> > UNION ALL
> > Select part2 as part, co2 as co, div2 as div, qty2 as qty
> > >From myfile
> > UNION ALL
> > Select part3 as part, co3 as co, div3 as div, qty3 as qty
> > >From myfile
> > Order by part
> >
> > You use a view in the same way you would use a LF.
> >
> > Once finish, simply use SQL statement DROP VIEW <name> to
> > delete the view.
> >
> > An alternative to doing this in CL, is to use the "execute
immediate"
> > SQL method in RPG:
> > For example:
> >  /free
> >     myString = "CREATE VEIW....."
> >  /end-free
> > c/exec sql
> > c+   execute immediate :myString
> > c/end-exec
> >
> > then define the view in your F-Specs with the USROPN option,
> > and open the file manually.
> >
> > Just my two-pence worth.  Let us know how you get on.
> >
> > Regards,
> > Terry
> 
> Privileged and Confidential.  This e-mail, and any attachments there
to,
> is intended only for use by the addressee(s) named herein and may
contain
> privileged or confidential information.  If you have received this
e-mail
> in error, please notify me immediately by a return e-mail and delete
this
> e-mail.  You are hereby notified that any dissemination, distribution
or
> copying of this e-mail and/or any attachments thereto, is strictly
> prohibited.
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.


This message may contain confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. Although the company 
has taken reasonable precautions to ensure no viruses are present in this 
e-mail, the company cannot accept responsibility for any loss or damage arising 
from the use of this e-mail or attachments. Any views or opinions presented in 
this e-mail are solely those of the author and do not necessarily represent 
those of the company.