RE: OPNQRYF a flat file?

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Dan Bale wrote:

>I was trying to avoid copying the flat file to an 
>externally described work file.

Understandable - why handle the records multiple times.  Of course,
depending on *how* you get the flat file, you could simply put the records
in the externally described one to begin with...

>There's just *gotta* be a way to use OPNQRYF to do 
>this, don't ya think?

I would guess that you need to MAPFLD every field in your FORMAT.

>And, yes, I thought for a split second of using FMTDTA, 
>although I have zero experience with it.  So, please 
>educate me with your examples.

You probably want a tag along sort instead of an addrout sort, so I'll start
there.
Essentially, you place the sort specs in a source file and run FMTDTA,
pointing to the member you stored the specs.  The specs themselves have 4
main parts.  I'll describe them after showing a simple tag along:

HFILE     30A         X                     
I U  15  19EQC518                 BTNPA     
FNC  28  57                       NAME      
FDC                                         

Coincidentally, each of these lines makes up one section <g>.  

The H spec describes the type of sort (FILE=tag along), how many columns
we're sorting on (30), ascending or descending (A) and whether or not to
keep the control field (X=no).  You need exactly one H spec per sort.

The I specs describe record inclusion tests.  This spec reads "include the
record if the Unpacked number between columns 15 and 19 is equal to the
character value 518."  The BTNPA is a comment.  You can have multiple I
specs, and you can use O (omit) specs as well.  This is how you tune the
selection performance.  If you intend to include a small fraction of the
total records, use I specs.  If you intend to omit a large fraction of
records, use O specs.  If you have a mix, be sure to Omit the ones you want
dropped before Including the rest. <g>

The FN specs define the control fields.  What sort does is to build this
"prefix" of control data in front of each record you want sorted.  The
prefix consists of all the FN columns appended together.  Then sort sorts on
those columns (it knows how many because we told it so on the H spec.)  This
spec reads "sort the data in Normal sequence (as specified in the H spec)
based on the Character portion of the input record from columns 28 through
57.  The "BTNAME" is a comment.

The FD specs describe the data fields you want to "tag along" with the
control fields.  In this case, we want all the data in the record, so we
don't specify any columns.  If you wanted a subset of the record, you would
specify the fields here (by column), as many as you need.

So, this sort will include all records where the area code equals 518,
append the customer name onto the front of the entire customer record, sort
the first 30 columns (the name) and drop the first 30 columns (X in H spec).
The result is a new customer master file, including only the records we
want, but having the exact same layout as went in.

Here's one with an omit:
HFILE     60A         X               
O P 273 277GEC000002500           CUR A/R 
I U   1   5EQC00518               PNPA
FNC 170 179                       ZIP 
FNC 148 167                       CITY
FOC  28  57                       NAME DESCEND
FDC                                   

Note that absolutely everything about sort is column sensitive; you need to
count the digits in your packed fields, etc.

Buck Calabro
Commsoft; Albany, NY
"Nothing is so firmly believed as
 that which we least know" -- Michel Montaigne
Visit the Midrange archives at http://www.midrange.com
+---
| 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
+---