|
View previous topic :: View next topic
|
| Author |
Message |
umanaga
New User
.jpg)
Joined: 09 Apr 2007
Posts: 33
Location: India
|
|
|
|
I have two files with data as below
| Code: |
X002027174
X002267035
X002846116
|
| Code: |
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035021000000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000
|
I need to join these 2 files, while joining i need to fetch fields from below recs in 2nd file.
| Code: |
X00202717401
X0020271740201
|
o/p as below
| Code: |
X002027174 008 F00
X002267035 001 F01
X002846116 007 blanks |
is it possible? |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Yes, probably.
What are the positions of the fields you need? What RECFM/LRECL of files? |
|
| Back to top |
|
|
umanaga
New User
Joined: 09 Apr 2007
Posts: 33
Location: India
|
|
|
|
Key is of 10 bytes 1,10 in both files.
in second file after 11,2 can be 01 or 02. 01 will always be one associated with key, 02's can be multiple.
To o/p i need 1,10 byte key, from 01 record i need 45,3 bytes. On 02 record which has value '01' in postion 13,2 need 47,3
i hope its clear..thanks for throwing a hope.. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
On what condition you need?
|
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Assuming your inpputs are correct
| Code: |
//SRTJK EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//JNA DD *
X002027174
X002267035
X002846116
//JNB DD *
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035021000000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000
//OUT DD SYSOUT=*
//T1 DD DSN=&&T1,SPACE=(TRK,(5,5)),
// DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT JKFROM TO(OUT) USING(CTL1)
/*
//CTL1CNTL DD *
JOINKEYS F1=JNA,FIELDS=(1,10,A)
JOINKEYS F2=JNB,FIELDS=(1,10,A)
REFORMAT FIELDS=(F2:1,80)
INREC IFTHEN=(WHEN=(11,2,CH,EQ,C'01'),
BUILD=(1:1,10,X,12:45,3)),
IFTHEN=(WHEN=(11,2,CH,EQ,C'02'),
BUILD=(1:1,10,X,12:47,3)),
IFTHEN=(WHEN=NONE,
BUILD=(1:1,10,4X))
OPTION EQUALS
SORT FIELDS=COPY
/* |
Output is
| Code: |
X002027174 008
X002027174 F00
X002027174 F00
X002267035 001
X002267035 F01
X002846116 007 |
|
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| umanaga wrote: |
Key is of 10 bytes 1,10 in both files.
in second file after 11,2 can be 01 or 02. 01 will always be one associated with key, 02's can be multiple.
To o/p i need 1,10 byte key, from 01 record i need 45,3 bytes. On 02 record which has value '01' in postion 13,2 need 47,3
i hope its clear..thanks for throwing a hope.. |
If you have multiple 02 records, do they ALL have the same value at pos 47? or does it vary with each 02 record?
ex:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+
MY-KEY01 01 ABC
MY-KEY01 02 DEF
MY-KEY01 02 GHI
MY-KEY01 02 IJK
|
Do you want your output to look like this?
| Code: |
MY-KEY01 ABC DEF GHI IJK (merging all 02 records into 1 record)
|
or
| Code: |
MY-KEY01 ABC DEF (only 1st 02 record value is taken)
|
|
|
| Back to top |
|
|
umanaga
New User
Joined: 09 Apr 2007
Posts: 33
Location: India
|
|
|
|
Multiple 02 look like below.
| Code: |
----+----1----+----2----+----3----+----4----+----5----+
MY-KEY01 01 ABC
MY-KEY01 0201 DEF
MY-KEY01 0202 GHI
MY-KEY01 0203 IJK
|
i want o/p like
| Code: |
| MY-KEY01 ABC DEF (DEF is to be taken from 02 having 01 in 13,2) |
|
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
The following DFSORT JCL will give you the desired results. I assumed that you had a typo for the record X002267035 as it did NOT have '01' at pos 13.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035020100000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000
//INB DD *
X002027174
X002267035
X002846116
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,10,A)
JOINKEYS F2=INB,FIELDS=(1,10,A)
REFORMAT FIELDS=(F1:1,14)
INREC IFOUTLEN=20,
IFTHEN=(WHEN=INIT,BUILD=(1,10,22:11,4)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(21:SEQ=1),RECORDS=2),
IFTHEN=(WHEN=GROUP,BEGIN=(21,1,ZD,EQ,1),PUSH=(11:22,4),RECORDS=2),
IFTHEN=(WHEN=(21,1,ZD,EQ,2),OVERLAY=(15:22,4))
OUTFIL REMOVECC,NODETAIL,SECTIONS=(1,10,TRAILER3=(1,20))
//*
//JNF1CNTL DD *
INCLUDE COND=(11,2,CH,EQ,C'01',OR,11,4,CH,EQ,C'0201')
INREC IFTHEN=(WHEN=(11,2,CH,EQ,C'01'),BUILD=(1,10,X,45,3)),
IFTHEN=(WHEN=(11,2,CH,EQ,C'02'),BUILD=(1,10,X,47,3))
//* |
The output from this is
| Code: |
X002027174 008 F00
X002267035 001 F01
X002846116 007
|
|
|
| Back to top |
|
|
|
|
|
