Syncsort -display recs in file A,which are matched in File B

xpower · New User Joined: 07 May 2006 Posts: 35

Dear all,

I have two files.
File A as follows:
A1
A1
A2
A2
A3
FILE B as follows:
A1
A3
B1

i want to the results are: that is the recs in file a,which are all matched with the recz in fileb.
A1
A1
A3

I don't know whether my request is clear or not.
Thanks ahead.

Best Regards

ankvardhan · New User Joined: 05 Sep 2008 Posts: 14 Location: Mumbai

Hi,

By using JoinKeys you can matched the records of both the files and display or write the same.

Syntax:
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=File A,DISP=SHR
//SORTJNF2 DD DSN=File B,DISP=SHR
//SORTOUT DD DSN=Output file ,DISP=SHR,
// DCB=(LRECL=18,RECFM=FB),UNIT=SYSDA,
// SPACE=(TRK,(10,1000))
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,2,A),SORTED
JOINKEYS FILES=F2,FIELDS=(1,2,A),SORTED
REFORMAT FIELDS=(F1:1,X,F2:1,Y)
OPTION COPY

Thanks,
Ank

dbzTHEdinosauer · Posted: Thu Sep 16, 2010 1:48 pm

ankvardhan,

please learn to use the bbcode (link here)

Anuj Dhawan · Posted: Thu Sep 16, 2010 3:53 pm

Okay, for the given sample inputs try this:

xpower · New User Joined: 07 May 2006 Posts: 35

Thank you so much, Sir.

yes, i tried and it worked. by the way, how to display the recs not matched in different files. in other words, the recs not matched in file a shuold be displayed in file c, and the recs not matched in file b should be displayed in file d.

Best Regards

ankvardhan · New User Joined: 05 Sep 2008 Posts: 14 Location: Mumbai

Anuj,
F2 is defined to merge, the matched records of both the files.
O/P is Like:
FileA FileB
A1 A1
A1 A1
A3 A3

From your JCL
O/P is like
A1
A1
A3

It means the same in both the cases, only difference is F2 includes the matched records of FileB also.

Anuj Dhawan · Posted: Thu Sep 16, 2010 7:07 pm

Anuj Dhawan · Posted: Thu Sep 16, 2010 7:11 pm

I've just noticed, this thread is in DFSort part of the Forum, Please note there is a difference the way JOINKEYS syntax is allowed in SyncSort and DFsort and I can not check if this will work with DFSort or not as I'm not logged on to the LPAR uses DFSort. For example, DFSort would use F1=IN1 while what I post is SyncSort Syntax. But you said previous solution worked for you..so I leave it to you (and Frank, ofcourse).

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

xpower,
Try below job for DFSort. Its just another way to get the desired results. The solution Anuj provided earlier,gives the same result as well.

Below job uses DFSort's 1 byte join indicator('?') to determine which records match and which ones doesn't.

'B' - in the join indicator indicates that the key was found in F1 and F2.
'1' - in the join indicator indicates that the key was found in F1, but not in F2.
'2' - in the join indicator indicates that the key was found in F2, but not in F1.

Frank Yaeger · Posted: Thu Sep 16, 2010 11:45 pm

xpower · New User Joined: 07 May 2006 Posts: 35

Anuj Dhawan,

thank you for your idea.

sqlcode1,

Appreciate your help. It worked as well.

Frank,

Thank you for your link. very useful for me.

Best Regards

Anuj Dhawan · Posted: Fri Sep 17, 2010 7:18 pm

Thank you Frank, that's a good tip for the day. Hope I didn't bother you much...

.

Have a good one,

Frank Yaeger · Posted: Fri Sep 17, 2010 9:22 pm

Anuj,

You didn't bother me at all.

foolsbrain · New User Joined: 15 Apr 2010 Posts: 11 Location: chennai

hi, I tried the above code and it works fine for me.
In the above example, file1 contains A1 twice and file2 contains A1 only once. What should i do to get A1 only once (i.e same number of occurences as of file2) from file1?

Frank Yaeger · Posted: Sat Oct 16, 2010 1:26 am

It's not clear what you're asking for. You need to show a better example with the differrent variations you want to handle, and explain the complete rules for getting from input to output.

foolsbrain · New User Joined: 15 Apr 2010 Posts: 11 Location: chennai

File1 contains
A1
A1
B1
C1
A1
C1
C1
D1

File2 contains
A1
B1
C1
B1
C1

My requirement is to write the matched records from File1 and it should look like

A1 -> Should come only once, as A1 is present only once in file2
B1
C1
C1 -> should come only twice, because in file2 C1 is present only twice even though C1 is present thrice in file1.

foolsbrain · New User Joined: 15 Apr 2010 Posts: 11 Location: chennai

so, the above request cannot be achieved?

daveporcelan · Posted: Tue Oct 19, 2010 8:30 pm

Achived by you or someone else?

Have you tried?

I was waiting for you to try then help you with errors and issues.

Frank Yaeger · Posted: Tue Oct 19, 2010 11:25 pm

dick scherrer · Posted: Tue Oct 19, 2010 11:33 pm

Hi Frank,

If i understand, the output should contain only the records that are in both files.

If there is an unequal number of records with the same key, the "extras" should be discarded from either file.

Frank Yaeger · Posted: Tue Oct 19, 2010 11:43 pm

By those rules, shouldn't D1 be in the output? It isn't. I think file2 is the controlling file, but I don't understand exactly how. The example shown doesn't even indicate which input file the output records come from so it's very confusing. We shouldn't have to guess what the OP wants.

dick scherrer · Posted: Tue Oct 19, 2010 11:56 pm

Hi Frank,

I believe D1 is excluded because it has no match.

foolsbrain · New User Joined: 15 Apr 2010 Posts: 11 Location: chennai

Hi Dick,
You are right, the output should contain only the records that are in both files. D1 is rejected because it is not present in File2.

Need to write the O/P using File1 records.

dick scherrer · Posted: Fri Oct 22, 2010 8:17 pm

Hello,

foolsbrain · New User Joined: 15 Apr 2010 Posts: 11 Location: chennai

Since we are only matching the key fields of File1 with File2, we can keep any of the matching records.

For example, if A1 is present 5 times in File1 and 3 times in File2, in the output we only need A1 thrice, we can discard any two A1 records.