Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 9:16 pm
by Dhiren
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.
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:24 pm
by skolusu
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
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:33 pm
by Dhiren
Hi Kolusu,
I can use DFSORT solution as well.
Your help is appreciated.
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:38 pm
by BillyBoyo
Can you show post the sysout from a sort step, please, so we can see what level of the product you have?
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:39 pm
by skolusu
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
//*
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:44 pm
by Dhiren
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
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 10:58 pm
by skolusu
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
Re: Merge two rows into one from same file
Posted:
Wed Sep 19, 2012 11:02 pm
by Dhiren
Thanks Kolusu,
I have re-posted in Syncsort forum and I do apologize for the error.
Thanks,
Dhiren.