|
View previous topic :: View next topic
|
| Author |
Message |
carlmgu
New User
Joined: 26 Aug 2005
Posts: 8
|
|
|
|
hi members,
I have a file having following data:
| Code: |
----+----1----+----2--
12345678900000000010CC
12345678900000000010AA
12345678900000000020BB
12345678900000000025CC
12345678900000000015AA
22222222220000000005AA
22222222220000000015CC
22222222220000000005CC
|
Is there any way to get output like this:
| Code: |
----+----1----+----2----+----3----+----4----+-
12345678900000000025AA0000000020BB0000000035CC
22222222220000000005AA0000000000BB0000000020CC
|
thanks in advance |
|
| Back to top |
|
 |
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| whatis the maximum number of unique second key occurance is possible?(AA,BB,CC.. will it be always three?) |
|
| Back to top |
|
|
carlmgu
New User
Joined: 26 Aug 2005
Posts: 8
|
|
|
|
| The second key always is three. |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| carlmgu wrote: |
| The second key always is three. |
RESIZE operator looks right candidate... |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| Escapa wrote: |
| carlmgu wrote: |
| The second key always is three. |
RESIZE operator looks right candidate... |
Escapa,
Did you notice that OP's input is missing BB record for 222222222 key.
Carlmgru,
was that a typo ? and you always have AA , BB , CC records for each key at position 1-10 and your summed field is from 11-10 in ZD format?
Please Post the LRECL and RECFM and all the fields formats |
|
| Back to top |
|
|
carlmgu
New User
Joined: 26 Aug 2005
Posts: 8
|
|
|
|
input LRECL=22, RECFM=FB
1-10 primary key
11-20 amount
21-22 second key
output LRECL=46, RECFM=FB
not all primary keys have a second key
| Code: |
----+----1----+----2--
12345678900000000010CC
12345678900000000010AA
12345678900000000020BB
12345678900000000025CC
12345678900000000015AA
22222222220000000005AA
22222222220000000015CC
22222222220000000005CC
33333333330000000050CC
44444444440000000050AA
44444444440000000025BB
44444444440000000025BB
|
| Code: |
----+----1----+----2----+----3----+----4----+-
12345678900000000025AA0000000020BB0000000035CC
22222222220000000005AA0000000000BB0000000020CC
33333333330000000000AA0000000000BB0000000050CC
44444444440000000050AA0000000050BB0000000000CC
|
|
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
carlmgu,
Is your input file sorted on primary key already?
Thanks, |
|
| Back to top |
|
|
carlmgu
New User
Joined: 26 Aug 2005
Posts: 8
|
|
|
|
| The input file is already sorted on primary key |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
Here's a DFSORT job that will do what you asked for:
| Code: |
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file (FB/22)
//SORTOUT DD DSN=... output file (FB/46)
//SYSIN DD *
INREC IFOUTLEN=46,
IFTHEN=(WHEN=(11,10,CH,EQ,C' '),OVERLAY=(11:10C'0')),
IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),
BUILD=(1,10,23:11,10)),
IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),
BUILD=(1,10,35:11,10))
OPTION COPY
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,10,
TRAILER3=(1,10,TOT=(11,10,ZD,TO=ZD,LENGTH=10),C'AA',
TOT=(23,10,ZD,TO=ZD,LENGTH=10),C'BB',
TOT=(35,10,ZD,TO=ZD,LENGTH=10),C'CC'))
|
|
|
| Back to top |
|
|
carlmgu
New User
Joined: 26 Aug 2005
Posts: 8
|
|
|
|
Thanks Frank!
I changed the code INREC because the job ended with 0C7
| Code: |
INREC IFOUTLEN=46,
IFTHEN=(WHEN=(21,2,CH,EQ,C'AA'),
BUILD=(1,10,11,10,C'AA',10C'0',C'BB',10C'0',C'CC')),
IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),
BUILD=(1,10,10C'0',C'AA',11,10,C'BB',10C'0',C'CC')),
IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),
BUILD=(1,10,10C'0',C'AA',10C'0',C'BB',11,10,C'CC'))
|
My problem is solved. Thanks All. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
In some cases blank fields for ZD values will cause a problem.
I added the following IFTHEN clause to my job above to take care of that:
| Code: |
IFTHEN=(WHEN=(11,10,CH,EQ,C' '),OVERLAY=(11:10C'0')),
|
It's simpler than what you did, although that will work too. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
Alternatively, you could use UFF in TOT instead of ZD to handle the blanks directly:
| Code: |
//S2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file (FB/22)
//SORTOUT DD DSN=... output file (FB/46)
//SYSIN DD *
INREC IFOUTLEN=46,
IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),
BUILD=(1,10,23:11,10)),
IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),
BUILD=(1,10,35:11,10))
OPTION COPY
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,10,
TRAILER3=(1,10,TOT=(11,10,UFF,TO=ZD,LENGTH=10),C'AA',
TOT=(23,10,UFF,TO=ZD,LENGTH=10),C'BB',
TOT=(35,10,UFF,TO=ZD,LENGTH=10),C'CC'))
|
|
|
| Back to top |
|
|
|
|
|
