RE: how to create logical file with one key over two fields

Joel,

If I am understanding correctly this may be what you want.

First, use SQL to create a view:
create view scott.junk as (select a.*, tdremk as upc from
trucklib.dispmstr as a where tdremk <> '' union select b.*, tddes2 as
upc from trucklib.dispmstr as b where tddes2 <> '')

This is using one of my files so I could verify it was working but I
think you can see what it is doing - it selects all fields from the file
and adds a new field called upc that is filled with either tdremk or
tddes2 (your 2 different upc fields) depending which side of the union
they are coming from. This view will have 2 records for 1 in the PF if
both fields are non-blank. You can create in index if you need it
keyed.

Then I created this RPG program to use the view:

fjunk if e k disk rename(junk : junkfmt)

/Free
read junk;
dsply tddisp;

*Inlr = *On;
Return;
/End-Free

If this isn't exactly what you need maybe it can be modified to get you
there.

Scott

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, August 23, 2012 12:42 PM
To: Midrange Systems Technical Discussion
Subject: RE: how to create logical file with one key over two fields

Yes it does work to create a multi-format LF: (names have been changed -
expanded - to make them readable even though they wont compile as such)

(There is a nice example in "Database DDS" by Charlie Massoglia)

Filename: ProductByUPC1or2

R ProductRec1 PFILE(ProductByItem#)

UPC RENAME(UPC1field)
K UPC
S UPC COMP(NE 0)


R ProductRec2 PFILE(ProductByItem#)

UPC RENAME(UPC2field)
K UPC
S UPC COMP(NE 0)




ProductByItem# file:

Item# UPC1 UPC2
AAA 111111111 0
BBB 777777777 0
CCC 0 4444444444


So if I read file "ProductByUPC1or2",

the first record read is item# AAA, followed by item#CCC, and then
item#BBB.

It works perfectly, except:

Problems are:

1) I am forced to name each field in the DDS. I would like it to
pull in all fields so the file is interchangeable in pgms with the PF.
If someone adds a field to the product file in the future, the pgms
would not get the new fields.

2) S/W like DBU and Query/400 don't handle multi-format LFs very
well. Query doesn't even seem to see the records. DBU shows them as a
long flat record (no fields).

Is it possible to get this to work so it looks like one record format to
pgms?? And to NOT have to name each field that I want included in the
LF; ie I would like to take ALL fields from the PF without individually
naming each field?

Thanks!