Re: Can SQL "simulate" a multiple format logical file with a join? -- MIDRANGE-L

To clarify, the field INVDATA would actually be something like INVCHRFLD1 || DIGITS(INVDECFLD1) || etc. with AS INVDATA for the alias.

At 10:35 AM 4/30/2004, you wrote:

GA, I would say yes, with a little work.

You want to use UNIONs - a separate SELECT for each PFILE of you multi-format logical. Add a "format indicator" field in each SELECT. This indicator should put the records in the same order as the PFILEs are. The rest of the fields in each PFILE should be concatenated into a single character (or hex?) field, long enough for the file with the longest data.

Something like

SELECT INVDIS, INVSCH, INVSTU, INVDAT, INVNUM, '0' AS INDIC, INVDATA
    FROM INVOICE
UNION
SELECT PMTDIS, PMTSCH, PMTSTU, PMTDAT, PMTNUM, '1', PMTDATA
    FROM PAYMENT
ORDER BY INVDIS, INVSCH, INVSTU, INVDAT, INVNUM, INDIC

Then, as you fetch the records, have DS's that break out the data in INVDATA and PMTDATA. Depending on the value of INDIC, move the respective data field into its DS.

I think you want the indicator last in the key.

HTH
Vern

At 09:27 AM 4/30/2004, you wrote:

Esteemed listers,

I am experimenting with a multiple format logical file:

     A          R INVOICE                   PFILE( FT320 )
     A** Key
     A          K INVDIS
     A          K INVSCH
     A          K INVSTU
     A          K INVDAT
     A          K INVNUM
     A**--------------------
     A*
     A          R PAYMENT                   PFILE( FT310 )
     A** Key
     A          K PMTDIS
     A          K PMTSCH
     A          K PMTSTU
     A          K PMTDAT
     A          K PMTNUM
     A**--------------------

As you may be able to surmise, the two physical files share the same key
structure, and the idea behind this type of logical is that I can just set
lower limits (RPG's SETLL) on District, School, and Student, and read by
key through the filename, and get the invoice and payment records in date
/ number order.  Defined like this, it works very well for my purposes.
(Just so you know, beyond the key structure, the record formats for these
two physicals are very different.)

Had a loop thrown at me yesterday that essentially requires me to turn
this into a join logical file.  Each format needs to join to another file,
and use one of the joined file's fields as an additional key field, a
FamilyID, inserted before the xxxSTU key field.  Two problems - only
fields from the primary file in a join logical can be a key field, and a
join logical can have only one record format.

Because this multiple format logical is being used to populate a subfile,
I can't build a workfile.  Can SQL do this, so there is always a view /
index available without having to rebuild it each time I want to populate
a subfile?

TIA, GA


__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.