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.

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.

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.