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
 

 

joinkeys with no repeated records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Sat Apr 11, 2009 12:17 pm    Post subject: joinkeys with no repeated records
Reply with quote

Hi All,
I am new to this forum.I need to solve a issue. I have compared two files and joined them using JOINKEYS. I am facing a issue regarding this. I ve joined from column 5 to column 10.


consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791

2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

Desird output file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

but i am gettin output file as
ddd 123456 kkkk
ddd 123456 ssss
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

My code is
JOINKEYS FILES=F1,FIELDS=(5,6,A)
JOINKEYS FILES=F2,FIELDS=(5,6,A)
REFORMAT FIELDS=(F1:1,11,F2:12,4)
SORT FIELDS=COPY

It is repeated four times i need to get only twice.Please help me out.
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Sat Apr 11, 2009 3:53 pm    Post subject:
Reply with quote

widia,

Hello and welcome to the forums. icon_smile.gif

The JOIN operation does a cartesian product and since you have duplicate keys in both the files, you will get the records repeated. You might need to remove duplicates from either of your input files before the JOIN operation.

From the sample data posted above, Posn 1-11 seem to be the same in both the input files. Why would you need the first 11 positions from file-1 and NOT from file-2? You need to provide a little more realistic data which covers all the possible input scenarios.
Back to top
View user's profile Send private message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Mon Apr 13, 2009 5:26 pm    Post subject: Reply to: joinkeys with no repeated records
Reply with quote

Hi arcvns,

As per my requirement, the input file can have duplicate keys. The Second input file is processed by first input file and i get additional information. So i append certain fields from first file and additional information from second file.

consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791


2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr


Here 234 and 567 makes difference in first file. They are processed in second file and i get 'KKKK' for ddd 123456 name1 234 and 'ssss' for ddd 123456 name1 567 .So in my requirement keys can repeat but i need to join without repetation in output file.

Thanks in advance.
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad

PostPosted: Tue Apr 14, 2009 4:03 pm    Post subject:
Reply with quote

widia,
Either you can sort the i/p file without duplicates before join or sort the output file generated from the join to avoid the duplicates.

Thanks
Krishy
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Tue Apr 14, 2009 6:04 pm    Post subject:
Reply with quote

widia,

Will there be non-matching keys in file-1 or file-2? Your sample data has only matching records and it makes difficult to make out how your actual data would be. Also in your example, the 2nd input file and the output file are one and the same. icon_lol.gif
Back to top
View user's profile Send private message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Thu Apr 16, 2009 10:48 am    Post subject: Reply to: joinkeys with no repeated records
Reply with quote

There will not be any non-matching keys as my first input file is processed to get second input file. The desired output file is not exactly same as second input file.It is concatenation of first and second input file. icon_sad.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Apr 16, 2009 8:51 pm    Post subject:
Reply with quote

Hello,

If the second file is data pulled from the first file, why is it needed?

If you post some more realistic and more complete "input" data (rather than alphabet soup) and post what you want as the actual output from the sample input, someone may have a suggestion.
Back to top
View user's profile Send private message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Fri Apr 17, 2009 3:43 pm    Post subject: Reply to: joinkeys with no repeated records
Reply with quote

Hi Dick,
To be more precise i ll give you the sample data,
consider file1
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09

File2
Rec 1: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 2: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 3: ddd 067457868 Neha 234old-st 768queen-st reason:updated

Output file
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated

So the first file is processed to get reason in second file. Here last field date is different for the first input file.Joinkeys are perfect when the record is present once. But when it is repeated twice, i get four records in desired output file.Sorry for the inconvenience.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Apr 18, 2009 1:12 am    Post subject:
Reply with quote

Hello,

I still do not understand why file 2 is created. . . icon_confused.gif

Why can file 1 not be read and the output file written as needed with the appropriate reason?
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Mon Apr 20, 2009 10:08 pm    Post subject:
Reply with quote

Widia,

I'm with Dick on this one. How is File 2 any different than your desired output from the JOIN processing? Isn't File 2 exactly what you are already looking for? If not, then you need to provide additional details and a better example of your desired output.
Back to top
View user's profile Send private message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Tue Apr 21, 2009 8:17 am    Post subject: Reply to: joinkeys with no repeated records
Reply with quote

Hi,

I can give u additiona info for input like this, hope this helps
File 1:
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09


File 2:
Rec 1: ddd 089376578 Error : 0000 reason:notmatched
Rec 2: ddd 089376578 Error : 0000 reason:notmatched
Rec 3: ddd 067457868 Error : 0000 reason:updated


Output file:
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated

I need to write only reasons from second file.Hope now the issue can be solved. icon_sad.gif
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Wed Apr 22, 2009 8:26 pm    Post subject:
Reply with quote

Here are 2 SyncSort for z/OS jobs that will do what you asked:
Code:
//STEP1  EXEC PGM=SORT           
//SORTJNF1 DD *                                     
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST 04/17/09 
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST 04/18/09 
DDD 067457868 NEHA 234OLD-ST 768QUEEN-ST 04/16/09   
//SORTJNF2 DD *                                     
DDD 089376578 ERROR : 0000 REASON:NOTMATCHED       
DDD 089376578 ERROR : 0000 REASON:NOTMATCHED       
DDD 067457868 ERROR : 0000 REASON:UPDATED           
//SORTOUT  DD SYSOUT=*                               
//SYSOUT   DD SYSOUT=*                               
//SYSIN    DD *                                         
  JOINKEYS FILES=F1,FIELDS=(5,9,A)                   
  JOINKEYS FILES=F2,FIELDS=(5,9,A)                   
  REFORMAT FIELDS=(F1:1,50,F2:27,18)                 
  INREC OVERLAY=(69:SEQNUM,8,ZD,RESTART=(1,68))     
  SORT FIELDS=COPY                                   
  OUTFIL INCLUDE=(69,8,ZD,EQ,1),BUILD=(1,41,51,18)   
/*                                                   

or
Code:
//SYSIN    DD *                                       
  JOINKEYS FILES=F1,FIELDS=(5,9,A)                 
  JOINKEYS FILES=F2,FIELDS=(5,9,A)                 
  REFORMAT FIELDS=(F1:1,50,F2:27,18)               
  SORT FIELDS=(1,68,CH,A)                           
  DUPKEYS FIELDS=NONE                               
  OUTREC FIELDS=(1,41,51,18)                       
/*                     


Here is the output that is produced from both of the above applications:
Code:
DDD 067457868 NEHA 234OLD-ST 768QUEEN-ST  REASON:UPDATED
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST REASON:NOTMATCHED
DDD 089376578 WIDIA 574ROYAL-ST 234NEW-ST REASON:NOTMATCHED
Back to top
View user's profile Send private message
widia

New User


Joined: 04 Mar 2009
Posts: 6
Location: Chennai

PostPosted: Fri Apr 24, 2009 3:32 pm    Post subject: Reply to: joinkeys with no repeated records
Reply with quote

Thanks Alissa Margulies.. icon_smile.gif it is working fine. Thanks a lot..
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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