Re: Alternate collating sequence w/ OPNQRYF

["Erickson, Jon" <JonErickson@xxxxxxxxxxxxxxxxxxxx>]

Success!

It's certainly NOT pretty, but I thought the MAPFLD could handle it.

Here's the situation:
1.      I have a PF with multiple date fields, 6 characters in length, using
MDY format.
2.      Multiple LF's which substring the 6 character date fields into
separate 2 character YY, MM, DD fields, which are used as key fields.  These
LF's have unique select/omits.
3.      Multiple CLP's using OVRDBF's from one LF to another as well as
OPNQRYF's with date selection criteria prior to calling an RPG.  e.g. 

OPNQRYF    FILE((SPLOOTS)) QRYSLT('DUYMD$ *LE ' || &YYMMDD$ || ' + 
             *AND SPPRA *NE "9" *AND SPPRA *NE "8"')             + 
             KEYFLD(*FILE)                                       + 
             MAPFLD((DUY 'SPDUYY' *ZONED 2 0)                    + 
                    (DUM 'SPDUMM' *ZONED 2 0)                    + 
                    (DUD 'SPDUDD' *ZONED 2 0)                    + 
                    (DUYMD$ '(DUY * 10000)                       + 
                             (DUM * 100)                         + 
                              DUD' *ZONED 6 0))                  + 
             SEQONLY(*YES)                                         


My first task was, of course, to make sure the file(s) were processed in
date sequence.  Secondly, perform the date selection either in the RPG or
OPNQRYF.

So I created an Alternate Collated Sequenced, (ACS) logical to replace the
logical which was keyed in YY, MM, DD.  

See  http://www.mindspring.com/~gatibennett/AlternateCollatingSequence.htm
<http://www.mindspring.com/~gatibennett/AlternateCollatingSequence.htm>  for
some ACS info.  Note: Cobol is used as an example and the 100 year window
defined is 50 through 49,  where I followed IBM's  window of 40 through 39.
You can pick your own range, dependant upon your business.

Since most of the CLP's perform OVRDBF's from one LF to another.  The
question arose:  Would I need to create corresponding ACS LF's for those
referenced in the OVRDBF?   The answer is YES, due to the key structure and
different record formats.

This brings me to my second task, date selection.  Initially, I tried to
move the date selection from the OPNQRYF to the converted RPGIV code, but
due to level indicators and such, I wasn't able to duplicate the production
version' output.  So I went back to look at the CLP with the OPNQRYF's,..  

Here is my solution:
OPNQRYF    FILE((SPLOOTSA)) QRYSLT('DUCYMD$ <= ' || &CYMD$ || '  + 
             *AND SPPRA *NE "9" *AND SPPRA *NE "8"')             + 
             KEYFLD(*FILE)                                       + 
             MAPFLD((DUCN '%XLATE(SDUDEC Y2KCENT)' *ZONED 1 0)   + 
                    (DUY 'SPDUYY' *ZONED 2 0)                    + 
                    (DUM 'SPDUMM' *ZONED 2 0)                    + 
                    (DUD 'SPDUDD' *ZONED 2 0)                    + 
                    (DUCYMD$ '(DUCN * 1000000) + (DUY * 10000) + + 
                              (DUM * 100) + DUD' *ZONED 7 0))    + 
             SEQONLY(*YES))

1)      I used CVTDAT to convert my YYMMDD to CYMD
2)      I used the %XLATE Built In Function on the decade field of the ACS
to translate as follows: Values 4, 5, 6, 7, 8, & 9 translated to 0.  Values
0, 1, 2, & 3 translated to 1.
a)      This required the creation of the Y2KCENT conversion table,
(TYPE(*CVT)).
i)       CRTTBL TBL(Y2KCENT) SRCFILE(*PROMPT) TEXT('Year 2000 Century digit
from decade Xlation')
ii)      Make the following changes for numeric & character conversions:
                Hex    Hex  
                Input  Output
                 00      01  
                 01      01  
                 02      01  
                 03      01  
                 04      00  
                 05      00  
                 06      00  
                 07      00  
                 08      00  
                 09      00
                 F0      F1  
                 F1      F1  
                 F2      F1  
                 F3      F1  
                 F4      F0  
                 F5      F0  
                 F6      F0  
                 F7      F0  
                 F8      F0  
                 F9      F0

Viola!  Your ready to move on to the next Y2k headache.


Regards,
~~~~~~~~~~~~~~~~~~~~~
Jon A. Erickson
Viking Industries, Inc.
 <http://www.vikingindustries.com> http://www.vikingindustries.com
~~~~~~~~~~~~~~~~~~~~~
 <<Jon Erickson.vcf>>

Jon Erickson.vcf