Page 3 of 3

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Sat Oct 18, 2008 1:05 am
by claywilly
I really appreciate your patience.

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Sat Oct 18, 2008 3:17 am
by Frank Yaeger
I've been answering questions all day and I have to get on to some other work. I've asked Kolusu to take a look at this and see if he can help. He has a lot more patience and tenacity than I do for these kinds of complex questions (and he's very clever too).

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Sat Oct 18, 2008 3:26 am
by claywilly
I wish him luck.

BTW. The JCL code that was posted awhile back does work. We just need to splice things together... unless there is a different approach altogether.

thanks again.

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Sat Oct 18, 2008 6:14 am
by skolusu
The following DFSORT/ICETOOL JCL will give you the desired results. You only showed me the output you wanted for final output but not for the nomatch file. I just assumed that you only wanted the data that is present in OPVL file. Make sure to have disp=MOD for the file T1

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//PSAM     DD DSN=INPUT.PSAM,DISP=SHR                       
//OPVL     DD DSN=INPUT.OPVL,DISP=SHR                       
//OPPH     DD DSN=INPUT.OPPH,DISP=SHR                       
//TEMP     DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(60,20),RLSE)
//OUT      DD SYSOUT=*                                       
//NOMATCH  DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY FROM(PSAM) TO(TEMP) USING(CTL1)                       
  SORT FROM(OPVL) TO(TEMP) USING(CTL2)                       
  SORT FROM(OPPH) TO(TEMP) USING(CTL3)                       
  SORT FROM(TEMP) USING(CTL4)                               
//CTL1CNTL DD *                                             
  OUTREC BUILD=(01:1,11,                                     
                13:82,10,                                   
                24:92,16,SFF,ZD,LENGTH=15,                   
                40:+0,TO=ZD,LENGTH=15,                       
                56:+0,TO=ZD,LENGTH=15,                       
                90:C'100')                                   
/*                                                           
//CTL2CNTL DD *                                             
  INCLUDE COND=(182,2,CH,EQ,C'PN')                           
  SORT FIELDS=(182,16,CH,A)                                 
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),         
  SECTIONS=(182,16,                                         
  TRAILER3=(01:187,11,                                       
            13:146,10,                                       
            40:TOT=(101,16,SFF,ZD,LENGTH=15),               
            56:15C'0',                                       
            90:C'010'))                                     
/*                                                           
//CTL3CNTL DD *                                           
  INCLUDE COND=(131,11,CH,NE,C' ')                         
  SORT FIELDS=(131,11,CH,A)                               
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),       
  SECTIONS=(131,11,                                       
  TRAILER3=(01:131,11,                                     
            40:15C'0',                                     
            56:TOT=(291,16,SFF,ZD,LENGTH=15),             
            90:C'001'))                                   
/*
//CTL4CNTL DD *                                             
  OPTION EQUALS                                             
  INREC OVERLAY=(94:7C'0',C'1')                             
  SORT FIELDS=(1,11,CH,A)                                   
  SUM FIELDS=(40,15,ZD,56,15,ZD,90,3,ZD,94,8,ZD)             
  OUTFIL FNAMES=OUT,INCLUDE=(90,3,ZD,GT,100),REMOVECC,       
  BUILD=(01:01,23,                                           
         24:24,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         40:40,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         56:56,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         72:24,15,ZD,SUB,40,15,ZD,SUB,56,15,ZD,             
         EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-)),               
  HEADER1=(01:'PA NUMBER',                                   
           15:'END DATE ',                                   
           28:'AUTH. AMOUNT',                               
           41:'OPVL CLOSED AMT',                             
           59:'OPPH EXPD AMT',                               
           77:'REM.BALANCE')                                 
                                                             
  OUTFIL FNAMES=NOMATCH,REMOVECC,                           
  INCLUDE=(90,1,ZD,EQ,0,AND,91,1,ZD,EQ,1),                   
  BUILD=(01:01,23,                                           
         24:40,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-)),   
  HEADER1=(01:'PA NUMBER',                                   
           15:'END DATE ',                                   
           28:'AUTH. AMOUNT')                               
/*                                                           


The out file will have

PA Number        End Date    Auth. Amount    OPVL Closed Amt.     OPPH Expended Amt.   Rem.Balance
D0000000020    03/31/1997      +47000.00           +0.00             +17319.30      +29680.70
D0000000022    04/30/1995     +100000.00           +0.00             +3276.50       +96723.50
D0000000023    04/30/1998      +18000.00           +0.00             +1838.20       +16161.80
N1000003319    11/15/1997     +750000.00         +128.46             +0.00          +749871.54


The nomatch will have

PA NUMBER     END DATE     AUTH. AMOUNT
F2000002042 07/30/2008        +64550.00
N1000003488 02/22/2007         +3629.25
N1000004520 02/15/2008        +46099.66
N1000007362 01/10/2008        +71642.94
N1000009176 11/25/2003          +277.45
N3000001358 02/21/2006          +326.38
X4000000219 05/29/2002          +884.17


if you need a different output for nomatch file let me know and I will create it

Hope this helps...

Cheers

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Mon Oct 20, 2008 9:46 pm
by claywilly
Hi Skolusu,

I am getting all zero amounts in the OPVL Closed Amount field and no records in the NoMatch file.

I am looking into it.

If you notice something before I do, please let me know..

Other than that, It looks great!

Thanks.

Re: Help with Icetool, Splice, Sum and Calculate complex sor

PostPosted: Mon Oct 20, 2008 10:38 pm
by skolusu
claywilly,

I used the sample data you supplied in this post

dfsort-icetool-icegener/topic1130-10.html#p4269

You should have atleast found N1000003319 having 128.46 in the OVPL Closed amount column

If you want to check for the nonmatch records change CTL4CNTL to the following and rerun the job and see the contents at pos 90. If you find a record with 010 or 011 , they are your unmatched records


//CTL4CNTL DD *                                            
  OPTION EQUALS                                            
  INREC OVERLAY=(94:7C'0',C'1')                            
  SORT FIELDS=(1,11,CH,A)                                  
  SUM FIELDS=(40,15,ZD,56,15,ZD,90,3,ZD,94,8,ZD)
  OUTFIL FNAMES=OUT
/*


Kolusu

Re: Help with Icetool, Splice, Sum and Calculate complex sort

PostPosted: Tue Oct 21, 2008 12:46 am
by claywilly
Ok. I found the problem.

In an earlier post I had the PN number position at 182, this should have been 187. My mistake.

I adjusted the code and the job ran with the expected results.

Here is the code I changed:
//CTL2CNTL DD *                                     
  INCLUDE COND=(187,2,CH,EQ,C'PN')                 
  SORT FIELDS=(187,16,CH,A)                         
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),
  SECTIONS=(187,16,                                 
  TRAILER3=(01:192,11,                             
            13:146,10,                             
            40:TOT=(101,16,SFF,ZD,LENGTH=15),       
            56:15C'0',                             
            90:C'010'))                             

The "182" was changed to "187" and the "187" was changed to "192".

Now I am getting the correct amounts in the OPVL CLosed Amount and
a ton of records in the NoMatch file.

I really appreciate both you and Frank for all your hard efforts.
Once again you have helped me tremendously.

Thanks again.