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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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.