RE: What would be your SQL solution?

Frank,

Need more information.

Specifically, what makes a record valid and what fields are being pulled from 
file1 and file2?  Also, don't quite understand what you are saying about file2, 
"keyed on FHDT to convert FHDT from 001 to ABC, then keyed on FHOT to convert 
FHOT from 002 to XYZ."

Lastly, what happens if there is no corresponding record in file1 or file?  Is 
there always one record or could there be more than one?

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
> fkany@xxxxxxxxxxxxxxxxxx
> Sent: Thursday, April 07, 2005 11:40 AM
> To: CN=RPG programming on the AS400/O=iSeries
> Subject: RE: What would be your SQL solution?
> 
> 
> 
> 
> FILEA is actually a logical file keyed on FHDDAT.
> 
> Ok, so lets get rid of RPGPGM1.  So how can RPGPGM2 be coded 
> using SQL to
> get all the data needed with in the specified date range?  I 
> hope the below
> pseudocode helps.  It shows what the original program was 
> doing.  It's the
> process of building fileb that takes the longest.  Once fileb 
> is built, the
> report only takes a few seconds to print.  If you need more 
> details, just
> let me know.
> 
> The subprocedure collects all the records needed and any associated
> information about the record.  I was thinking that maybe if I 
> could narrow
> down the number of records that the subprocedure had to read 
> through, that
> maybe the subprocedure would complete faster.
> 
> Begin Subprocedure
> 
> fhddat      setll filea
> 
>       start loop:
> 
>       read filea by fhddat(delivery date)
> 
>       validate record
> 
>       if record is good
>             get more information about record from 
> file1(keyed on FHOT &
> FHPRO)
>             get more information about record from 
> file2(keyed on FHDT to
> convert FHDT from 001 to ABC, then keyed on FHOT to convert 
> FHOT from 002
> to XYZ)
>             call program to gather more information from various files
>             write record to fileb
>       endif
> 
>       end loop:
> 
> End Subprocedure
> 
> 
> 
> 
>