Re: Multiple formats in PF or LF

I agree with Dan, why would you want to do this.

But we have exactly the scenario you describe and if you try and use sql

SQL7003 - SQL cannot process a file unless it has only one format.
There may be a way around this but I have not investigated.

To do it create the 3 physical files.

Then create a logical like this example;

A UNIQUE
A R XXXDTRR PFILE(XXXDTRPF)
A K DTRAUD
A*
A R XXXCTRR PFILE(XXXCTRPF)
A K CTRAUD
A*
A R XXXGTRR PFILE(XXXGTRPF)
A K GTRAUD


Cheers,

Don






From: "Dan" <dan27649@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 30/12/2020 10:35 AM
Subject: Re: Multiple formats in PF or LF
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



I'm working with a head cold right now, so not sure if this is what you're
looking for.

I didn't quite make the connection in my last email, so allow me to
clarify.

I don't understand why anyone would consider a single "flat file' with
multiple record formats for a header/detail/payment type of file. The
System/36 died a long time ago. Alan's suggestion to try SUBSTR gives me
shudders, like, shoot me now kind of shudders. Do you hate the people who
will inherit your code? ;-)

Have you considered creating three DDS PFs / SQL tables: Header, Detail,
Payment

Having defined those, you can create a multiple-format LF using DDS. But,
per my last email, I don't know whether SQL would play nice with that.

It sounds to me like your solution might be to JOIN the PFs / tables in a
CREATE VIEW, and eschew the multi-format LF altogether. You might be able
to use lateral correlation in place of joining the files in the view.

- Dan

On Tue, Dec 29, 2020 at 6:52 PM Dan <dan27649@xxxxxxxxx> wrote:

A PF can have only one record format, but an LF can have multiple record
formats.

Because it's been ages since I last used them, I can only point you to
IBM's DDS for Physical and Logical files Reference. Search for
"multiple-format logical files in DDS" to find a code sample.

I'm not sure whether SQL can process a multiple format LF. Hopefully,
others can assist.

- Dan

On Tue, Dec 29, 2020 at 6:37 PM <smith5646midrange@xxxxxxxxx> wrote:

I'm starting to think that I am searching for something that doesn't
exist.

I thought there was a way of having a single "flat file" and breaking

it

into multiple record formats in the DDS so the individual records could

be

accessed via SQL

For example: a flat file that would contain sales data

HdrFmt
RcdType
SaleNumber
SaleDate
DtlFmt
RcdType
SaleNumber
Item
Quantity
Price
PayFmt
RcdType
SaleNumber
PaymentType
Amount

If I wanted to query all of the detail records to find out what items

were

sold, I could do something like select item, count(*) from flatfile

where

rcdtype = 'DTL' group by item

Am I just thoroughly confused thinking I have seen something like this

in

the past? If not, can someone please provide the DDS to show me how it

is

done? Nothing that I have tried has worked. I know I can create

separate

PFs and copy the appropriate data to each file via a CPYF *NOCHK

selecting

the appropriate rcdtype but that defeats the purpose of what I am

trying

to
do.