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

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Michael_Schutte@xxxxxxxxxxxx
> Sent: 17 May 2006 19:39
> To: RPG programming on the AS400 / iSeries
> Subject: Re: Use SQL instead of RPG?
> 
> Just curious, why does it need to be in the CL?  I thought you were
> wanting
> to put the results into a subfile?
> 
> Anyway... the only thing that I know that you can do is and as I know
it
> ...
> 
>    On the command line type:   Go Query
>    Take Option 10
>    Take Option 1
>    Create new query.
>    You'll now be in something similar to Query/400.
>    Specify one file
>    Exit and save the query.
>    Now convert the query to SQL, Put a 10 on the new query.
>    You may keep the same name.
>    Now put a 2 on the query to edit the SQL.
>    Now copy your SQL statement in there.
>    Then in the CL, you have to use the STRQMQRY to execute the SQL
that
> you
>    created.
> 
> 
> Michael Schutte
> 
> 
> 
> 
>              steema@diskhaven.
>              com
>              Sent by:
To
>              rpg400-l-bounces@         "RPG programming on the AS400 /
>              midrange.com              iSeries"
<rpg400-l@xxxxxxxxxxxx>
>
cc
> 
>              05/17/2006 02:18
Subject
>              PM                        Re: Use SQL instead of RPG?
> 
> 
>              Please respond to
>               RPG programming
>               on the AS400 /
>                   iSeries
>              <rpg400-l@midrang
>                   e.com>
> 
> 
> 
> 
> 
> 
> Charles's Idea was the answer. Now I would like to put this SQL into
an
> CL.
> 
> > What is a pivot table?  I've heard the term, now my curiosity is
> aroused.
> >
> > rob@xxxxxxxxx wrote:
> >> I think Charles has the best grasp on it.  Ideally one would
normalize
> >> this database to stop this ridiculousness.  Gotta go, but there's a
> >> brain
> >> cell somewhere shrieking out a buzzword to pop out before I duck
and
> >> run:
> >> Pivot table perhaps?
> >>
> >> Rob Berendt
> >
> > --
> > ---------------------------------
> > Booth Martin
> > http://www.Martinvt.com
> > ---------------------------------
> > --
> > 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 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 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.