Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Match, FB, keys in different places, duplicates + No match r

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Thu Jan 20, 2011 11:32 pm    Post subject: Match, FB, keys in different places, duplicates + No match r
Reply with quote

Hi,
I was going through this technique in Smart DFSORT Tricks.

Consider this case where File1 has duplicates and File2 doesn't.
Each file has a name and amount key.
File1 has the amount field in positions 1-4 and the name field in positions 6-13.
File2 has the amount field in positions 12-15 and the name field in positions 3-10.
Here are the FB input records:

Input File1
Code:
0003 Vicky O1
0007 Vicky O2
0003 Vicky O3
0015 Frank O1
0005 Carrie O1
0005 Carrie O2
0005 Carrie O3
0006 Carrie O4
0008 Holly O1
0103 David O1
0103 David O2

Input File2
Code:
A Karen 0003
D Holly 0008
X Carrie 0005
R Vicky 0003
L Mary 1023


We want to keep all of the records in File1 that have a match in File2 on the amount and name fields. But in this
case, we have duplicates in File1. The 0003 Vicky, 0005 Carrie and 0008 Holly records in File1 have a match in
File2, so we want to keep the two 0003 Vicky records, three 0005 Carrie records, and one 0008 Holly record from
File1. The 0007 Vicky, 0015 Frank, 0006 Carrie and 0103 David records in File1 do not have a match in File2, so
we don't want to keep those records.

The output file will have the following records:
Code:
0003 Vicky O1
0003 Vicky O3
0005 Carrie O1
0005 Carrie O2
0005 Carrie O3
0008 Holly O1


Here's a DFSORT JOINKEYS job for this:

Code:
//JK6 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/22)
//IN2 DD DSN=... input file2 (FB/15)
//SORTOUT DD DSN=... output file (FB/22)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,4,A,6,8,A)
  JOINKEYS F2=IN2,FIELDS=(12,4,A,3,8,A)
  REFORMAT FIELDS=(F1:1,22)
  OPTION COPY
/*


I would like to know, is it possible to retain the unpaired rows from File 1 as well in the same sort step? With a default value of mine, when it is not paired?
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 21, 2011 12:14 am    Post subject:
Reply with quote

Quote:
I would like to know, is it possible to retain the unpaired rows from File 1 as well in the same sort step? With a default value of mine, when it is not paired?


It's not clear what you mean by this. What exactly do you want for output? How many output files do you want?

Please show an example of the records you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Fri Jan 21, 2011 4:24 pm    Post subject: Reply to: Match, FB, keys in different places, duplicates +
Reply with quote

OUtput Files - 1
RECFM - FB
Input LRECL - 80
Output LRECL - 81

Input - 1 (Will have duplicates)
Code:
0003 Vicky  O1
0007 Vicky  O2
0003 Vicky  O3
0015 Frank  O1
0005 Carrie O1
0005 Carrie O2
0005 Carrie O3
0006 Carrie O4
0008 Holly  O1
0103 David  O1
0103 David  O2


Input - 2(will not have duplicates)
Code:

A Karen  0003
D Holly  0008
X Carrie 0005
R Vicky  0003
L Mary   1023


Compare From Input 1 (1,4) with Input 2 (10,4)
On match, field 1 of LRECL 80 with field (1,1) from file in 81st position.
On No match, field 1 of LRECL 80 with field (1,1) as Z in 81st position.

Output
Code:
0003 Vicky  O1 R
0007 Vicky  O2 R
0003 Vicky  O3 R
0015 Frank  O1 Z
0005 Carrie O1 X
0005 Carrie O2 X
0005 Carrie O3 X
0006 Carrie O4 X
0008 Holly  O1 D
0103 David  O1 Z
0103 David  O2 Z
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Jan 22, 2011 2:06 am    Post subject:
Reply with quote

Quote:
Compare From Input 1 (1,4) with Input 2 (10,4)
On match, field 1 of LRECL 80 with field (1,1) from file in 81st position.
On No match, field 1 of LRECL 80 with field (1,1) as Z in 81st position.


This would NOT give you the output you show. For example,

Code:

File1 records
0003 Vicky  O1
0007 Vicky  O2
0003 Vicky  O3


1-4 (0003) in the first and third file1 records match 10-4 (0003) in both File2 records.
1-4 (0007) in the second file1 record does not match 10-4 (0007) in any File2 record.
So how do you get this for output:

Code:

0003 Vicky  O1 R
0007 Vicky  O2 R
0003 Vicky  O3 R


I think you really want to compare on the name field rather than the number field, but you need to explain exactly what you want to do with a good example of input and output, and a corresponding descriptions of the rules for getting from input to output, before I can help you.

Please take the time to state your requirement clearly and accurately.
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Sun Jan 23, 2011 5:22 pm    Post subject: Reply to: Match, FB, keys in different places, duplicates +
Reply with quote

You are right, excuse me for the mess up.

Input - 1 (Will have duplicates)
Code:

12345678901234
--------------
0003 Vicky  O1
0007 Vicky  O2
0003 Vicky  O3
0015 Frank  O1
0005 Carrie O1
0005 Carrie O2
0005 Carrie O3
0006 Carrie O4
0008 Holly  O1
0103 David  O1
0103 David  O2


Input - 2(will not have duplicates)
Code:

1234567890123
-------------
A Karen  0003
D Holly  0008
X Carrie 0005
R Vicky  0013
L Mary   1023


Compare From Input 1 (1,4) with Input 2 (10,4)
On match, file1 of LRECL 14 + file2's field (1,1) in 15th position.
On No match, file1 of LRECL 14 + file2's field (1,1) as Z in 15st position.

Output
Code:

123456789012345
---------------
0003 Vicky  O1A
0007 Vicky  O2Z
0003 Vicky  O3A
0015 Frank  O1Z
0005 Carrie O1X
0005 Carrie O2X
0005 Carrie O3X
0006 Carrie O4Z
0008 Holly  O1D
0103 David  O1R
0103 David  O2R


In Output, the Key 0007 and 0006 of Input 1 didn't have a match on Input 2, so they are defaulted to 'Z'
Code:
123456789012345
---------------
0007 Vicky  O2Z
0006 Carrie O4Z


This is what, I'm trying to achieve.
I'm aware that the output other than the above two can be achieved through DFSORT Join keys.
Trying to get these two records as well.
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Jan 24, 2011 11:14 pm    Post subject: Reply to: Match, FB, keys in different places, duplicates +
Reply with quote

Frank Yaeger or any other expert, need your advice on the above.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 25, 2011 12:26 am    Post subject:
Reply with quote

I just got to work. I suspect I'm in a different time zone than you are.

Given that you're in such a hurry to get an answer, you ought to take more time to state your requirement correctly. Your expected output does NOT make sense. You show R as the last output character for the 0103 records, but it should be Z since there's no 0103 record in file2.

Assuming your expected output should really be as follows:

Code:

0003 Vicky  O1A   
0007 Vicky  O2Z   
0003 Vicky  O3A   
0015 Frank  O1Z   
0005 Carrie O1X   
0005 Carrie O2X   
0005 Carrie O3X   
0006 Carrie O4Z   
0008 Holly  O1D   
0103 David  O1Z   
0103 David  O2Z   


You can use this DFSORT job to do what you want:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//SORTOUT DD DSN=...  output file
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,4,A)
  JOINKEYS F2=IN2,FIELDS=(10,4,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,14,F2:1,1,F1:15,8),FILL=C'Z'
  SORT FIELDS=(16,8,ZD,A)
  OUTREC BUILD=(1,15)
/*
//JNF1CNTL DD *
  INREC OVERLAY=(15:SEQNUM,8,ZD)
/*
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Tue Jan 25, 2011 1:12 pm    Post subject: Reply to: Match, FB, keys in different places, duplicates +
Reply with quote

Thanks for the answer and your patience, Frank Yaeger

Quote:
REFORMAT FIELDS=(F1:1,14,F2:1,1,F1:15,8),FILL=C'Z'
SORT FIELDS=(16,8,ZD,A)
OUTREC BUILD=(1,15)
/*
//JNF1CNTL DD *
INREC OVERLAY=(15:SEQNUM,8,ZD)
/*


I don't understand the significance of (15,8) and the Control card for overlay.
What it is for? Is it (15,1)?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jan 25, 2011 1:34 pm    Post subject:
Reply with quote

did you bother to look at the JNF1CNTL control card?

INREC OVERLAY=(15:SEQNUM,8,ZD)
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 25, 2011 11:37 pm    Post subject:
Reply with quote

HameedAli,

Since you wanted the records in their original order, I added a sequence number to the F1 input records so we could sort on them after the join.

The INREC statement in JNF1CNTL adds the sequence number at positions 15-22 in the F1 records.

The REFORMAT statement includes the sequence number at positions 16-23 in the joined records.

The SORT statements sorts on the sequence number at positions 16-23 in the joined records.

The OUTREC statement removes the sequence number.
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Wed Jan 26, 2011 2:30 pm    Post subject: Reply to: Match, FB, keys in different places, duplicates +
Reply with quote

Thanks a lot for the explanation, I understood it now.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 1 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts DFSORT to match two files and write John F Dutcher DFSORT/ICETOOL 6 Thu May 12, 2016 5:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us