RE: What would be your SQL solution?

Charles,

> what makes a record valid and what fields are being pulled from file1 and
file2?

OT,DT, FROMDATE, & TODATE entered by user.

A valid record will have:

For Report1A: OT = numeric literal > 0
              DT = numeric literal
1) an FHOT = to OT entered by the user
2) an FHDT = to DT entered by the user
3) an FHDT <> to OT entered by the user
3) an FHOT and FHPRO combination that doesn't already exist in the work
file for the report

For Report1B: OT = numeric literal > 0
              DT = *ALL
1) an FHOT = to OT entered by the user
2) an FHDT <> to OT enterd by the user
3) an FHOT and FHPRO combination that doesn't already exist in the work
file for the report

For Report1C: OT = *ALL
              DT = numeric literal > 0
1) an FHOT <> to DT entered by the user
2) an FHDT = to DT enterd by the user
3) an FHOT and FHPRO combination that doesn't already exist in the work
file for the report

For Report1D: OT = numeric literal > 0
              DT = *ALL
1) an FHOT = to OT entered by the user
2) an FHDT <> to OT enterd by the user
3) an FHOT and FHPRO combination that doesn't already exist in the work
file for the report

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

FILE2 holds data on our terminals. FHOT is considered origin terminal(OT)
and FHDT is considered destination terminal(DT).  For our Cookeville
terminal, we don't want 004 showing up on the report.  Instead, the program
chains out to FILE2 using FHOT or FHDT to get the alpha code instead of the
numeric code and the report will print CKV instead of 004.

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

There will always be a corresponding record in file1 and file2.

Frank










"Wilt, Charles" <CWilt@xxxxxxxxxxxx>@midrange.com on 04/07/2005 12:51:37 PM

Please respond to RPG programming on the AS400 / iSeries
       <rpg400-l@xxxxxxxxxxxx>

Sent by:    rpg400-l-bounces@xxxxxxxxxxxx


To:    "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc:

Subject:    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
>
>
>
>
>

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