Page 1 of 1

Sort on variable fields and FINDREP simultaneously?

PostPosted: Fri Feb 19, 2010 1:48 am
by Don Treadway
I'm trying to sort a file with comma and quote delimited variable length fields. I've got that working just fine.

 INREC PARSE=(%00=(STARTAFT=C',',ENDBEFR=C',',PAIR=QUOTE,FIXLEN=7),
             %01=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=2),               
             %02=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=3)),             
   OVERLAY=(189:%00,                                               
            196:%01,                                               
            198:%02)                                               
 SORT FIELDS=(189,7,CH,A,196,2,CH,A,198,3,CH,A)                     
 OUTREC BUILD=(1,188)                                               


I'd also like to do a find/replace at the same time to avoid making another pass at the data in a subsequent step. Any way to combine FINDREP with the above code? Any way I do it gives me an ICE107A error.

ICE107A 0 DUPLICATE, CONFLICTING, OR MISSING INREC OR OUTREC STATEMENT OPERANDS

Thanks much,
Don

Re: Sort on variable fields and FINDREP simultaneously?

PostPosted: Fri Feb 19, 2010 2:15 am
by Frank Yaeger
You haven't said what you want to use FINDREP for or at what stage of the process you want to use it, so I can only give you a general answer - use IFTHEN clauses, e.g.

   INREC IFTHEN=(WHEN=INIT,PARSE=(...),OVERLAY=(...)),
             IFTHEN=(WHEN=INIT,FINDREP=(...))


or whatever order you need them in.

You can use IFTHEN clauses with OUTREC or OUTFIL as well.

You can also use INREC, OUTREC and OUTFIL together to do multiple things (just remember that they are processed in that order).

Re: Sort on variable fields and FINDREP simultaneously?

PostPosted: Wed Mar 03, 2010 2:42 am
by Don Treadway
Thanks for the hint Frank.

I like to figure things out myself where possible so didn't give all the details. You pointed me in the right direction though and I was able to get it working.

In case it helps anyone else, here's what I was trying to do. I had a variable length input file, with commas as field delimiters and double quotes around each field. I needed to sort the input according to a key (in this case fields 2, 1,3 and 4) and also remove all instances of consecutive double quotes. This was to be used in a DB2 load and sorting beforehand would save me CPU and time versus a reorg after the load. DB2 doesn't like quotes around null values, hence the need to remove consecutive double quotes.

 
  INREC IFTHEN=(WHEN=INIT,                                     
        FINDREP=(INOUT=(C'""',C''))),                         
       IFTHEN=(WHEN=INIT,                                     
        PARSE=(%02=(ENDBEFR=C',',FIXLEN=3),                   
               %01=(ENDBEFR=C',',FIXLEN=7),                   
               %03=(ENDBEFR=C',',FIXLEN=2),                   
               %04=(ENDBEFR=C',',FIXLEN=2)),                 
        OVERLAY=(201:%01,                                     
                 208:%02,                                     
                 211:%03,                                     
                 213:%04))                                   
  SORT FIELDS=(201,7,CH,A,208,3,CH,A,211,2,CH,A,213,2,CH,A)   
  OUTREC BUILD=(1,200)