IBM Mainframe Forum

by **ibmmf4u** » Wed Apr 11, 2012 11:55 pm

Hi,

My requirement is to join two flat files which doesn't contain any matching keys in both the files. Please help me out in joining the two files.We are using syncsort 1.3.2.2R in our shop currently. Below mentioned were the files.

File1:-

Select all

TRANS1 TESTER1
TRANS2 TESTER2
TRANS3 TESTER3
TRANS4 TESTER4
TRANS5 TESTER5

File2:-

Select all

USER12
USER34
USER56
USER78
USER90

The expected output file should be like this:-

Select all

TRANS1 TESTER1 USER12
TRANS2 TESTER2 USER34
TRANS3 TESTER3 USER56
TRANS4 TESTER4 USER78
TRANS5 TESTER5 USER90

Kindly help me out!!

by **dick scherrer** » Thu Apr 12, 2012 12:07 am

Hello,

Suggest you append a sequence number to each file and then JOIN on this sequence number. When writing the output the sequence numbers would be discarded.

This assumes there will always be exactly the same number of records in both files (and that they are already in the order required.

by **enrico-sorichetti** » Thu Apr 12, 2012 12:24 pm

using irrelevant titles will most likely make people skip Your post
reducing the number of replies You will get.

by **ibmmf4u** » Thu Apr 12, 2012 10:23 pm

Hi Dick,

Thanks a lot . It helped me in joining the two files with the help of sequence number . Pasted below is the code that's working fine. Thanks a lot for your help once again.

Select all

//STEP010 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
TRANS1 TESTER1
TRANS2 TESTER2
TRANS3 TESTER3
TRANS4 TESTER4
TRANS5 TESTER5
//SORTOUT DD DSN=TEST.TEMP.DATASET,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//STEP020 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
USER12
USER34
USER56
USER78
USER90
//SORTOUT DD DSN=TEST.TEMPOUT.DATASET,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//STEP030 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.TEMP.DATASET,DISP=SHR
//SORTJNF2 DD DSN=TEST.TEMPOUT.DATASET,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(81,8,A),SORTED
JOINKEYS FILES=F2,FIELDS=(81,8,A),SORTED
REFORMAT FIELDS=(F1:1,20,F2:1,6)
SORT FIELDS=COPY
/*

I would also like to thank Alissa who provided me the manual which helped me in learning things much faster.

Hi Ernico,

Sure going forward will make sure to have an appropriate tittle.

Thanks,

by **dick scherrer** » Fri Apr 13, 2012 12:59 am

Good to hear it is working - thank you for letting us know

d

IBM Mainframe Forum

Need help on sort!!!

Need help on sort!!!

Re: Need help on sort!!!

Re: Need help on sort!!!

Re: Need help on sort!!!

Re: Need help on sort!!!