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

Very cool! The reason for the long field was that the remaining fields are probably different between the joined files. But this would be an even better solution, since the person writing the SELECT statement would not need to write the concatenations and especially not need to covert everything ot character. SQL would not have known to concatenate, the developer would have had to take care of it.

I assume you would add an ORDER by on the 1st 3 columns in your example. As I wrote in the other post, I THINK the "record selector" column should be last in the key and should force the same order as the multi-format logical did. Does that sound right?

Later
Vern

At 11:01 AM 4/30/2004, you wrote:

Vern,

Why one big field instead of little ones? And how would SQL know to
concat the field into one big thing? I'm thinking more like:

Select key1, key2, 'inv', invfld1, invfld2, invfld3, null, null, null
Union
Select key1, key2, 'pmt', null, null, null, pmtfld1, pmtfld2, pmtfld3

-Walden

------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Friday, April 30, 2004 11:36 AM
To: Midrange Systems Technical Discussion
Subject: Re: Can SQL "simulate" a multiple format logical file with a
join?

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.


_______________________________________________
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.