Page 1 of 1

How to subtract specific value from matching records

PostPosted: Thu May 10, 2012 8:04 pm
by Eswarbabu
Hi ,

Below is my query.

INPUT1:
AAAA,-000050
CCCC,-000100

INPUT2:
AAAA,-000050
CCCC,+000100

OUTPUT (For matching records on the first four columns, subtract (Input1-input2))

AAAA,000000
CCCC,-000200

I could successfully SUM for the above inputs with the help of EDIT MASKS, SIGNS operator. But for subtraction, i need ur help.

Re: How to subtract specific value from matching records

PostPosted: Thu May 10, 2012 9:21 pm
by skolusu
use the following DFSORT JCL
//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD *                                                     
----+----1----+----2----+----3----+----4----+----5----+----6----+---
AAAA,-000050                                                       
CCCC,-000100                                                       
//INB      DD *                                                     
AAAA,-000050                                                       
CCCC,+000100                                                       
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(1,4,A)                                   
  JOINKEYS F2=INB,FIELDS=(1,4,A)                                   
  REFORMAT FIELDS=(F1:1,12,F2:6,7)                                 
  INREC BUILD=(1,5,6,7,SFF,SUB,13,7,SFF,EDIT=(STTTTTT),SIGNS=(+,-))
//*

Re: How to subtract specific value from matching records

PostPosted: Thu May 10, 2012 9:37 pm
by Eswarbabu
Thanks Kolusu :)

I was trying this only. Use joinkeys to get the subtract fields in a single record and apply SUB with EDIT MASKS, SFF and sign operator.