Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 3:31 amWhere is this flag2? I see Code# and Flag1 and 4 amount columns. I do not see anything with a value of 4.
IBM Mainframe Forum
Mainframe Technical Support Forums
http://www.ibmmainframeforum.com/
Need help on merging two records from same file
https://www.ibmmainframeforum.com/syncsort-synctool/topic8173-10.html
Page 2 of 2
Posted: Thu Sep 20, 2012 3:31 amRe: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 3:55 amOn the input, up in the first post.
Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 5:48 amHere would be one possible way. This is with DFSORT, so let us know if you have "syntax" problems by pasting your full sysout in the Code tags.
You can "rationalise" the column numbers to minimise space used.
Output is:
You can "rationalise" the column numbers to minimise space used.
//SUMRECS EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(25:13C'0',40:13C'0',
55:13C'0',70:13C'0')),
IFTHEN=(WHEN=(6,1,CH,EQ,C'1'),
OVERLAY=(25:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'2'),
OVERLAY=(40:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'3'),
OVERLAY=(55:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'4'),
OVERLAY=(70:7,13,SFF,TO=ZD,LENGTH=13))
SORT FIELDS=(1,5,CH,A)
SUM FIELDS=(25,13,ZD,40,13,ZD,55,13,ZD,70,13,ZD)
OUTREC BUILD=(1,5,
25,13,ZD,M18,LENGTH=13,
40,13,ZD,M18,LENGTH=13,
55,13,ZD,M18,LENGTH=13,
70,13,ZD,M18,LENGTH=13)
//SORTIN DD *
121021 000079751.79
121014 000440494.52
121024-000241189.45
121321 000154520.73
121322 000002643.39
121323-000000000.06
121324-000227258.71
121424 000094288.24
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(25:13C'0',40:13C'0',
55:13C'0',70:13C'0')),
IFTHEN=(WHEN=(6,1,CH,EQ,C'1'),
OVERLAY=(25:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'2'),
OVERLAY=(40:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'3'),
OVERLAY=(55:7,13,SFF,TO=ZD,LENGTH=13)),
IFTHEN=(WHEN=(6,1,CH,EQ,C'4'),
OVERLAY=(70:7,13,SFF,TO=ZD,LENGTH=13))
SORT FIELDS=(1,5,CH,A)
SUM FIELDS=(25,13,ZD,40,13,ZD,55,13,ZD,70,13,ZD)
OUTREC BUILD=(1,5,
25,13,ZD,M18,LENGTH=13,
40,13,ZD,M18,LENGTH=13,
55,13,ZD,M18,LENGTH=13,
70,13,ZD,M18,LENGTH=13)
//SORTIN DD *
121021 000079751.79
121014 000440494.52
121024-000241189.45
121321 000154520.73
121322 000002643.39
121323-000000000.06
121324-000227258.71
121424 000094288.24
Output is:
12101 0.00 0.00 0.00 440,494.52
12102 79,751.79 0.00 0.00 -241,189.45
12132 154,520.73 2,643.39 -0.06 -227,258.71
12142 0.00 0.00 0.00 94,288.24
12102 79,751.79 0.00 0.00 -241,189.45
12132 154,520.73 2,643.39 -0.06 -227,258.71
12142 0.00 0.00 0.00 94,288.24
Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 7:38 pmHi Billy,
Thank you for helping me out. The output is in reverse order.
Output after running the job :
Expected output:
Thank you for helping me out. The output is in reverse order.
Output after running the job :
12101 0.00 79,751.79 0.00 0.00
12104 440,494.52 -241,189.45 0.00 0.00
12134 0.00 -227,258.71 0.00 0.00
12104 440,494.52 -241,189.45 0.00 0.00
12134 0.00 -227,258.71 0.00 0.00
Expected output:
12101 0.00 79,751.79 0.00 000440494.52
12102 000079751.79 0.00 0.00 -000241189.45
12132 000154520.73 0.00 -0.00 -000227258.71
12102 000079751.79 0.00 0.00 -000241189.45
12132 000154520.73 0.00 -0.00 -000227258.71
Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 7:55 pmMy sort cards (DFSort, by the way) with my test data (based on yours, but augmented) seems to do OK.
Can you show the sysout from your step and the input data that you used?
Also please explain what you mean by "reverse order".
Can you show the sysout from your step and the input data that you used?
Also please explain what you mean by "reverse order".
Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 8:16 pmHi Billy,
Based on your sort card you should get two rows for Code 1210 (One for flag1 value of 1 and one for flag1 value of 2).
For the first row (Flag1 of 1) , the associated Flag2 value is 4 therefore Amt4 should have a value and rest (Amt 1/2/3 should be zeroes).
Similarly for Code 1213, you should only get one row based on Flag1 value of 2 and all Amt column will have values based on value of flag2 (which in your case are 1,2,3,4).
when I ran the job, the Amount column is getting value based on Flag1 instead of flag2. ( Eg : for code 1210 flag1 2 and flag2 1 & 4) the rows wriiten was
12104 Amt1
12104 Amt2
Based on your sort card you should get two rows for Code 1210 (One for flag1 value of 1 and one for flag1 value of 2).
For the first row (Flag1 of 1) , the associated Flag2 value is 4 therefore Amt4 should have a value and rest (Amt 1/2/3 should be zeroes).
Similarly for Code 1213, you should only get one row based on Flag1 value of 2 and all Amt column will have values based on value of flag2 (which in your case are 1,2,3,4).
when I ran the job, the Amount column is getting value based on Flag1 instead of flag2. ( Eg : for code 1210 flag1 2 and flag2 1 & 4) the rows wriiten was
12104 Amt1
12104 Amt2
Re: Need help on merging two records from same file
Posted: Thu Sep 20, 2012 8:35 pmThanks Billy,
Input file used in the run had some bad data and it was causing few accounts to display in-correct data. I regenerated the input file and the results are as expected.
Thank you all for your help and it was very much appreciated.
Thank you.
Input file used in the run had some bad data and it was causing few accounts to display in-correct data. I regenerated the input file and the results are as expected.
Thank you all for your help and it was very much appreciated.
Thank you.