AW: Arrays

Hi,

there are different Redbooks, where you'll find examples in embedded SQL:
Who Knew You Could Do That with RPG IV? A Sorcerer's Guide to System Access
and More
(Chapter 6)
http://www.redbooks.ibm.com/abstracts/sg245402.html?Open

Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
(Chapter 7)
http://publib-b.boulder.ibm.com/abstracts/sg246393.html?Open

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Douglas W. Palme
Gesendet: Dienstag, 29. März 2005 18:29
An: RPG programming on the AS400 / iSeries
Betreff: RE: Arrays


Thanks Rob, will definitely take a closer look at embedded sql.


On Tue, 29 Mar 2005 09:20:27 -0600, Joe Pluta wrote
> > From: Douglas W. Palme
> >
> > The whole reason behind this question is that I have to create a
> subfile
> > that will display based on what salesman number the user enters, YTD
> > hardgood sales (which is contained within the customer master file)
> and
> > then
> > Cylinder rent which would be a cumulative amount from the line item
> file
> > (which contains 1.3 million records) and then a calculated field
> showing
> > the
> > total, this has to be done for each different customer number assigned
> to
> > the salesman, and to top it off, I need the data displayed in
> descending
> > order LOL
>
> Okay, let's take just a moment and break this down.
>
> First, you need to process data in salesman/customer order.  As Bruce
> mentioned, a single logical over the line detail by salesman/customer
> will allow you to process this data.  You would use the SETLL
> command to position the cursor at the beginning of the records for
> that salesman, then read through the file using the READE command.
>  You would use something we old-timers call "level break" logic to
> sum up the records for each customer.
>
> Now, if you were just showing it by salesman and customer, that
> would be pretty simple.  The fly in the ointment is that you need to
> sort it by a calculated field.  This is ALWAYS more difficult.  Back
> in the day, there were basically three ways to do it:
>
> 1. A work file.  Write the records to the work file, which would
> then be keyed by the appropriate value.
>
> 2. An array.  If you know the number of records will be small enough,
> you can use an array.  Build an array of data structures then sort
> it. In your case, since you're processing a single salesman, the maximum
> number of entries would be the number of customers.
>
> Okay, that's old school techniques.
>
> Today, we would use embedded SQL.  The select would look something like
> this (don't trust me on the syntax, there might be some grouping issues
> and so forth, but you get the general idea):
>
> SELECT CYLCUST, HARDYTD, SUM(CYLRENT) AS SUMRENT, HARDYTD+SUMRENT AS
> TOTAL FROM CYLLIN, CUSMST WHERE CYLSLS = SLSMN AND CUST = CYLCUST GROUP
> BY CYLCUST
>
> Use this in a cursor and then read through it, writing each record to
> the subfile.  For performance, you should still have the logical over
> your line item file by salesman/customer.
>
> Joe
>
> --
> 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.


--
Open WebMail Project (http://openwebmail.org)

--
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.