The data base trick… Building a multi-format logical file over a single physical file.

Most programmers have built Logical files to re-sequence or omit records in a physical file. Most programmers have created a logical JOIN file, which joins one file to another by the fields they have in common.

One older approach used on the System /38 and for some specialized AS400 programs was to intermix two physical files by a similar key, such as putting an employee master record in a logical file, then following it with various training records for the employee. Here both files would have the employee number in common. This was an approach to simplify printing of disparate files, by gathering together groups of records principally for printing programs.

This alternate approach is not to bring together different files, but rather to bring together different views of the same record, say a right hand view and a left hand view.

For example, say you have 2 unit-of-measure records, such as below:

A simple Unit of Measure Conversion file

From

From

To

To

Unit

Unit

Unit

Unit

Qty

Meas

Qty

Meas

1

KG

2.2

LB

Fmt FROM

1

LB

16

OZ

Fmt FROM

 

What we really desire is not force users to create two records when one will do. What if the reverse record is miskeyed? Why clutter up the data base with these superfluous records? What if someone only creates one of the two records required?

Using a data base trick - the file appears

From

From

To

To

Unit

Unit

Unit

Unit

Qty

Meas

Qty

Meas

1

KG

2.2

LB

Fmt FROM

2.2

LB

1.0

KG

Fmt TO

1

LB

16

OZ

Fmt FROM

16

OZ

1

LB

Fmt TO

 

The trick is to redefine the right hand fields Qty and Measure to look like the left hand. And provide a key to this file that can be used by application programs.

Then write your RPG program to ignore the formats in the logical file, but only just read the fields in the file.

The programming rules for the application is …

If the From Qty is 1.00, then Multiply.

If the From Qty is not 1.00, then Divide.

Here is the source of the logical file:

A* Unit of Measure File

A*

A* An example of multi-format file built

A* over a single physical

A*

A R FROM PFILE(MFPUM)

A FROMQT R

A FROMUM R

A TOQT R

A TOUM R

A K FROMUM

A K TOUM

A*****************************

A R TO PFILE(MFPUM)

A FROMQT RENAME(TOQT)

A COLHDG('From' 'Unit' 'Qty ')

A FROMUM RENAME(TOUM)

A COLHDG('From' 'Unit' 'Meas')

A TOQT RENAME(FROMQT)

A COLHDG(' To ' 'Unit' 'Qty ')

A TOUM RENAME(FROMUM)

A COLHDG(' To ' 'Unit' 'Meas')

A K FROMUM

A K TOUM

Lets look at another example