IBM Mainframe Forum

Hi ,

I have a requirement where i will be matching 2 files with a key and pull the matched record along with other columns from file2 . see below for example.

File1 is primary, file1 will be compared with file 2 and pull the matched records and write an output in formatted way (123,456,A,) also i need unmatched record from file1 to output with space in the last byte like (234,567, ,)


I tried file joining but couldn't able to get the result. Kindly help to provide the sort card for this.


File 1

123456
234567


File2
123456XXXXXXA
534556xxxxxxZ


Expected output
--------------------------
123,456,A,
234,567, ,

Can you show the code you attempted. Knowing what you couldn't get working out of it will be the best way to target advice. It is a very simple issue.

Also it is not entirely clear what you want to do. You need to add commas as well?

Hi Billy,

I have tried with below sort card but what I get is merged of F1 & F2 is the format mentioned in the build.

say for example, I have employee-id in F1 and employee-id along with name in F2, I need to match employee-id of F1 with F2 and pull both (employee-id and name) information.
Also if an emp id in F1 is not present in f2, then i need to write emp-id + name as spaces in the same output file.

the result should be comma separated as mentioned in example, Hope I'm clear now. Please let me know if you need any more information.



Example

File 1




File2



Expected output




Code'd

Thanks for the clarification.

From the sample date you have shown, the key on the File 2 is in the same position as the File 1, so that needs to change.

You are showing your sample data as already being in key order. If that is the case, add SORTED,NOSEQCK to each of the JOINKEYS for whose file that is true (else the JOINKEYS will cause the file to be sorted again, wasting resources).

On the JOIN statement you have UNPAIRED,F1,F2,ONLY. That will get all unmatched records on F1 and all unmatched records on F2 and no other records. You want matched records, which you get with every JOIN which does not specify ONLY, and you don't want unmatched F2 records.

If data is consecutive in a BUILD (not "gaps" for automatic space-padding) it only confuses to specify column numbers.

There are only two pieces of data you need from File 2, the key, and the data you want to add. So you can use JNF2CNTL to limit the data from your File 2 for the join (reducing resource use).

For an unmatched record in the REFORMAT statement, data fields contain space (unless you specify a different FILL character), so nothing big you need to do with that.



That is your basic code. The JOIN statement will give you matched records, and unmatched F1 records. Where you are unmatched F1 records, the F2 data will be blank.

If you cut down on the amount of data to be processed from File 2:

,SORTED,NOSEQCK



Then change the REFORMAT statement:



You could look at using Sort Symbols on a SYMNAMES dataset, with a SYMNOUT as well, to specify a symbol for the comma. Then instead of three separate literals of the same value, you'd have one symbol with one value used three times. Makes changing things in the future easier and more accurate.

Bill,


Thanks a lot .. you are the man !!!

These all seems to be a piece of cake to you seriously.


Merry X-Mas Bill ... Thanks again...

Bill,

I have a doubt here that, you mentioned "JOIN UNPAIRED,F1" won't it pull only the upaired records of F1.

But how does it get both paired and unpaired of F1 along with a value from F2.

Am just trying to understand it more detail.

Please explain.