Help Comes from a long way(Charlie Massoglia)

[Pat Barber <mboceanside@xxxxxxxxxxxxxxxx>]

In an earlier message, I needed some help on a date situation
using Opnqryf and couldn't for the life of me remember how to
sort a 6 digit date(S/36 file) correctly. 

Charlie Massoglia, who apparently is in Singapore came through
with the correct answer and he saved me from a lot of very
aggravating trial and error to get the thing to work.

There are really two ways to do this but Charlie's idea of
the %digits to convert the numeric(zoned decimal) to a 
character field was the real trick here.... 

I got very lucky since this was an old S/36 file converted to DDS, 
it had several two byte "filler" fields that I used to %SST back 
to two character fields that could be used as KEY fields for the sort.

So I ended up using Charlie's idea but NOT having to use the alternate
format trick since I had available fields to map to....

               Thanks Charlie

Here is the answer to the problem on how to sort a six digit
date(correctly) using opnqryf:

Pat,

You can use the %DIGITS function to map the numeric field to a character
field and then use the %SUBST function to substring out the month, day,
and
year.  I don't have access to an AS/400 so I am not sure the syntax is
exactly
correct but try something like the following:

   OVRDBF FILE(WORKFILE) TOFILE(YOURFILE) SHARE(*YES)

   OPNQRYF FILE(YOURFILE) FORMAT(WORKFILE) KEYFIELD((CENTURY) (YEAR)
(MONTH)(DAY)) 

MAPFLD((CHARDATE '%DIGITS(NUMDATE)') (MONTH '%SST(CHARDATE 1 2)')
(DAY '%SST(CHARDATE 3 2)') (YEAR 'SST(CHARDATE 5 2)'))

   CALL PROGRAM

The DDS for the file WORKFILE would be identical to the file YOURFILE
with the three fields MONTH, DAY, and YEAR added to the DDS.  The file
WORKFILE
does not need to contain the field CHARDATE as that field is not used as
a key
field. I'm assuming the field CENTURY is already defined in the file
WORKFILE.
The file WORKFILE would never contain any data.  The RPG program would
use
the file name WORKFILE in the F-specs instead of YOURFILE.

I'm in Singapore right now and could not send a response directly to
Midrange-L for some reason.  I'd appreciate your forwarding this
response to
Midrange-L in case anyone else runs into the same problem.
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---