Re: Multiple formats in PF or LF

[Don Brown <DBrown@xxxxxxxxxx>]

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.