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)