How to subtract specific value from matching records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

How to subtract specific value from matching records

Postby Eswarbabu » Thu May 10, 2012 8:04 pm

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.
Eswarbabu
 
Posts: 9
Joined: Tue Dec 27, 2011 12:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to subtract specific value from matching records

Postby skolusu » Thu May 10, 2012 9:21 pm

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=(+,-))
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: How to subtract specific value from matching records

Postby Eswarbabu » Thu May 10, 2012 9:37 pm

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.
Eswarbabu
 
Posts: 9
Joined: Tue Dec 27, 2011 12:29 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post