IBM Mainframe Forum

by **Puffe** » Tue Oct 25, 2011 5:49 pm

Hi,

I have two files that are FB but different length.
File B has more records than file A.

File A (lrecl=19, key in position 6-11)
MS001196408208888AA
MS002196408221111BB
MS003196408222222BB
MS004196409239999BB

File B (lrecl=22, key in position 1-6)
1964082100112010-10-10
1964082100222010-11-01
1964082211222009-01-01
1964082222332009-02-01
1964092433442010-01-01

The join should be between the keys above.
I want all the records in file A (position 6-17) to be changed with the value in file B (position 1-12) if the key matches.

The result I want in file C is
MS001196408210011AA
MS002196408210022BB
MS003196408221122BB
MS004196409243344BB

Regards,

Mikael

by **Frank Yaeger** » Wed Oct 26, 2011 1:31 am

It's not clear what you want to do.

According to your description, the keys in file1 are:

196408
196408
196408
196409

and the keys in file2 are:

196408
196408
196408
196408
196409

so the first 3 keys in file1 match the first 4 keys in file2. Is that what you really have/want?

If so, do you want to replace based on the sequence number of duplicate keys, e.g.
file1 key1 record01 with file2 key1 record01
file1 key1 record02 with file2 key1 record02
...

If not, what do you want exactly?

Please state more clearly what it is you want to do.

by **Puffe** » Wed Oct 26, 2011 10:13 am

What I want is to change all records in File A with records from File B but only partly (position 6-17).

So record no 1 in File A
MS001196408208888AA
matches File B record no 1 with key 196408 and therefore the new record in File C should be (pos 1-5 from File A, pos 6-17 from File B, pos 18-19 from File A)
MS001196408210011AA

And for record no 2 in File A
MS002196408221111BB
matches File B record no 2 with key 196408 and therefore the new record in File C should be (pos 1-5 from File A, pos 6-17 from File B, pos 18-19 from File A)
MS002196408210022BB

And so on until all records in File A is changed.

Yes, File B have more keys (196408) than File A.

So every record in file A should get a unique value (position 6-17) from File B.

I hope I made it more clearly to You.

by **BillyBoyo** » Wed Oct 26, 2011 11:55 am

so the first 3 keys in file1 match the first 4 keys in file2. Is that what you really have/want?

If so, do you want to replace based on the sequence number of duplicate keys, e.g.
file1 key1 record01 with file2 key1 record01
file1 key1 record02 with file2 key1 record02

I still don't think you've have clarified this.

by **enrico-sorichetti** » Wed Oct 26, 2011 12:43 pm

lets reword the logic ...

Select all

file a
key0-some-value1-rest-of-record-for-file-a
key0-some-value2-rest-of-record-for-file-a

key1-some-value1-rest-of-record-for-file-a
key1-some-value2-rest-of-record-for-file-a
key1-some-value3-rest-of-record-for-file-a

key2-some-value1-rest-of-record-for-file-a
key2-some-value2-rest-of-record-for-file-a
key2-some-value3-rest-of-record-for-file-a

file b
key0-other-value1-rest-of-record-for-file-a
key0-other-value2-rest-of-record-for-file-a

key1-other-value1-rest-of-record-for-file-b
key1-other-value2-rest-of-record-for-file-b
key1-other-value3-rest-of-record-for-file-b
key1-other-value4-rest-of-record-for-file-b

key2-other-value4-rest-of-record-for-file-b
key2-other-value4-rest-of-record-for-file-b

output ( logic match)
key0-other-value1-rest-of-record-for-file-a
key0-other-value2-rest-of-record-for-file-a

key1 ????
key2 ????

how do You want to match things when the number of records with the same key is different ?
is it so difficult to answer ?
post the expected result for key1 and key2

by **Frank Yaeger** » Wed Oct 26, 2011 10:52 pm

do you want to replace based on the sequence number of duplicate keys

Mikael,

I think you're saying the answer to my question is yes. If so, then you can use a DFSORT JOINKEYS job like the following to do what you want:

Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/19)
//IN2 DD DSN=... input file2 (FB/22)
//SORTOUT DD DSN=... output file (FB/19)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(6,6,A,20,8,A)
JOINKEYS F2=IN2,FIELDS=(1,6,A,23,8,A)
REFORMAT FIELDS=(F1:1,5,F2:1,12,F1:18,2)
OPTION COPY
/*
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(6,6),PUSH=(20:SEQ=8))
/*
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,6),PUSH=(23:SEQ=8))
/*

For your input example, SORTOUT would have:

Select all

MS001196408210011AA
MS002196408210022BB
MS003196408221122BB
MS004196409243344BB

If there are other cases for which this doesn't do what you want, you need to show me an example of input and expected output for those cases and explain the "rules".

by **Puffe** » Fri Oct 28, 2011 11:26 am

That's exactly what I wanted Frank, so you got it right. Thanks a lot!!!!

IBM Mainframe Forum

Changing data when sorting two files

Changing data when sorting two files

Re: Changing data when sorting two files

Re: Changing data when sorting two files

Re: Changing data when sorting two files

Re: Changing data when sorting two files

Re: Changing data when sorting two files

Re: Changing data when sorting two files