Merge two rows into one from same file



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

Merge two rows into one from same file

Postby Dhiren » Wed Sep 19, 2012 9:16 pm

Hi,
I have multiple records for a given account id and trying to combine all records related to each
account into one single record. Number of records for each account may vary from 1 - 4. Can
someone please help to do this using syncsort?.

Input:
Fixed 19 record length file. No header & trailer record in the file.
3 - input fields with their record position
code Flag1 Flag2 Amount
(1-4) (5) (6)        (7-13)

1210  2   1          000079751.79
1210  1  4           000440494.52
1210  2  4          -000241189.45
1213  2  1           000154520.73
1213  2  4          -000227258.71


Expected output:
6 - output fields
Code# Flag 1 Amount Amount Amount  Amount
(1-4)  (5-5)  (6-18)   (19-31)  (32-44)   (45-57)

1210  1          0        0           0            000440494.52
1210  2         0         000079751.79   0 -000241189.45
1213  2         0         000154520.73   0 -000227258.71


Any help will be greatly appreciated.
Thank you.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merge two rows into one from same file

Postby skolusu » Wed Sep 19, 2012 10:24 pm

Dhiren wrote:someone please help to do this using syncsort?.


Dhiren,

This is a DFSORT forum , if you are seeking a syncsort solution , you need to post in the Syncsort/Synctool forum.

Thanks
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: Merge two rows into one from same file

Postby Dhiren » Wed Sep 19, 2012 10:33 pm

Hi Kolusu,

I can use DFSORT solution as well.
Your help is appreciated.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merge two rows into one from same file

Postby BillyBoyo » Wed Sep 19, 2012 10:38 pm

Can you show post the sysout from a sort step, please, so we can see what level of the product you have?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Merge two rows into one from same file

Postby skolusu » Wed Sep 19, 2012 10:39 pm

Dhiren wrote:Hi Kolusu,

I can use DFSORT solution as well.
Your help is appreciated.


Run the following DFSORT JCL and show me the complete sysout , so that we can determine the level of DFSORT you have

//STEP0100 EXEC PGM=SORT       
//SYSOUT   DD SYSOUT=*         
//SORTIN   DD *               
//SORTOUT  DD SYSOUT=*         
//SYSIN    DD *               
  SORT FIELDS=COPY             
//*
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: Merge two rows into one from same file

Postby Dhiren » Wed Sep 19, 2012 10:44 pm

SYNCSORT FOR Z/OS 1.4.0.1R U.S. PATENTS: 4210961, 5117495 (C) 2010 SYNCSO
xxxxxxxxxxxxxxxx z/OS 1.13.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 38026, MODEL 2817 713 LICEN
SYSIN :
INREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,1,ZD,RESTART=(1,5))),
IFTHEN=(WHEN=(20,1,ZD,EQ,1),BUILD=(1,5,6,1,64Z)),
IFTHEN=(WHEN=(20,1,ZD,EQ,2),BUILD=(1,5,1Z,6,1,63Z)),
IFTHEN=(WHEN=(20,1,ZD,EQ,3),BUILD=(1,5,2Z,6,1,62Z)),
IFTHEN=(WHEN=(20,1,ZD,EQ,4),BUILD=(1,5,3Z,6,1))
SORT FIELDS=(1,5,CH,A),EQUALS
SUM FIELDS=(6,8,14,8,22,8,30,8,38,8,46,8,54,8,62,4),FORMAT=BI
WER276B SYSDIAG= 1145003, 4723847, 4723847, 16826700
WER164B 18,724K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 18M BYTES USED
WER108I SORTIN : RECFM=FB ; LRECL= 19; BLKSIZE= 27987
WER073I SORTIN : DSNAME=ST.UST0JL.SELECT.SUM

WER246I FILESIZE 3,150 BYTES
WER054I RCD IN 45, OUT 36
WER072I EQUALS, BALANCE IN EFFECT
WER169I RELEASE 1.4 BATCH 0520 TPF LEVEL 0.1
WER052I END SYNCSORT - UST0JL2,SEP2905B,SORT,DIAG=C400,4241,E8B2,AC75,8C4F,68C
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merge two rows into one from same file

Postby skolusu » Wed Sep 19, 2012 10:58 pm

Dhiren wrote:SYNCSORT FOR Z/OS 1.4.0.1R U.S. PATENTS: 4210961, 5117495 (C) 2010 SYNCSO
xxxxxxxxxxxxxxxx z/OS 1.13.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 38026, MODEL 2817 713 LICEN
SYSIN :

WER052I END SYNCSORT - UST0JL2,SEP2905B,SORT,DIAG=C400,4241,E8B2,AC75,8C4F,68C


well you are using Syncsort and the question needs to be posted in other forum.

Thanks
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: Merge two rows into one from same file

Postby Dhiren » Wed Sep 19, 2012 11:02 pm

Thanks Kolusu,

I have re-posted in Syncsort forum and I do apologize for the error.

Thanks,
Dhiren.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post