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 duplicated keys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
juares castro

New User


Joined: 04 May 2012
Posts: 18
Location: Brazil

PostPosted: Fri Sep 23, 2016 5:58 am    Post subject: Joinkeys with duplicated keys
Reply with quote

Hi everyone!

I am trying do a joinkey in SYNCSORT with duplicated keys in both INfiles. Actually in this example we have identical files talking about keys:
pos. 4 length 3.

Here input files:

SORTJNF1
Code:
001111AAA
002222BBB
003333AAA
004333BBB
005333CCC
006444DDD


SORTJNF2
Code:
001111AAA
002222BBB
003333111
004333222
005333333
006444DDD


Using the code below:

Code:
JOINKEYS FILE=F1,FIELDS=(4,3,A)                                         
  JOINKEYS FILE=F2,FIELDS=(4,3,A)                                         
  JOIN UNPAIRED,F1,F2         
  REFORMAT FIELDS=(?,F1:1,9,F2:1,9)   
  SORT FIELDS=COPY                                                       
  OUTFIL FILES=01,INCLUDE=(1,1,CH,EQ,C'B'),BUILD=(2,18,62X)


we have the file below:

SORTOF01
Code:
001111AAA001111AAA
002222BBB002222BBB
003333AAA003333111
003333AAA004333222
003333AAA005333333
004333BBB003333111
004333BBB004333222
004333BBB005333333
005333CCC003333111
005333CCC004333222
005333CCC005333333
006444DDD006444DDD


As we can see we got duplicated records in SORTOF01. Records # 4, 5, 6, 8, 9 and 10 are duplicated.

I mean the result i would like have is:

SORTOF01
Code:
001111AAA001111AAA
002222BBB002222BBB
003333AAA003333111
004333BBB004333222
005333CCC005333333
006444DDD006444DDD


Please, how could i code SYNCSORT avoiding duplicated records?

Thanks in advance!
Coded'd
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Sep 23, 2016 8:19 am    Post subject:
Reply with quote

Please learn to use code tags.
Why do you have
Code:
 JOIN UNPAIRED, F1,F2
?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 23, 2016 10:48 am    Post subject:
Reply with quote

You have mentioned key positions as POS: 4, LENGTH: 3, but your input/output suggest otherwise.

Try this sort card:
Code:
  JOINKEYS FILE=F1,FIELDS=(3,4,A)
  JOINKEYS FILE=F2,FIELDS=(3,4,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(?,F1:1,9,F2:1,9)
  SORT FIELDS=(4,4,CH,A,13,4,CH,A)
  SUM  FIELDS=NONE
  OUTFIL FILES=01,INCLUDE=(1,1,CH,EQ,C'B'),BUILD=(2,18,62X)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Sep 23, 2016 12:12 pm    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

mistah kurtz,

If you use 3,4 as the keys, there are no duplicate keys, so there is evidence for OP being correct with 4,3 (their problem is with duplicate keys).

juares castro,

You are SORTing both files (each JOINKEYS SORTs its input unless told not to). You sample data doesn't show any reason for this (data is in key order). Consider using SORTED on the JOINKEYS statement(s).

It looks as though you just want to "side-by-side" the data. Both your input files are the same. Best way to do this is to generate a sequence number, appended to each record (for fixed-length records, prepended to the data for variable-length records) and using the sequence number as the key.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Sep 23, 2016 5:25 pm    Post subject: Re: Reply to: Joinkeys with duplicated keys
Reply with quote

Bill Woodger wrote:
Both your input files are the same.
Bill - Did you mean just for the key fields?
juares castro wrote:
Actually in this example we have identical files
juares castro,

Will you always have the same number of records in both the input files? Will you have the same keys appearing in the same order in both the input files?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Sep 23, 2016 9:00 pm    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

No, Arun. On inspection the data shown, including the key, is identical and is claimed to be identical as you noted.

And no, I don't know why this:

Code:
  OPTION COPY
  INREC BUILD=(1,9,1,9)


Wouldn't be sufficient, either.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Sep 23, 2016 9:20 pm    Post subject:
Reply with quote

Bill - Records 3,4 and 5 are different.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Sep 23, 2016 9:36 pm    Post subject: Re: Reply to: Joinkeys with duplicated keys
Reply with quote

I wanted to make sure this comment from Bill is Not missed and this should be what OP wanted:
Bill Woodger wrote:

It looks as though you just want to "side-by-side" the data. Both your input files are the same. Best way to do this is to generate a sequence number, appended to each record (for fixed-length records, prepended to the data for variable-length records) and using the sequence number as the key.

As Bill said, best way to do is to generate a sequence number and use it as a key.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Sep 23, 2016 9:40 pm    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

Thanks, Arun. Well, it wasn't a very good "inspection" then, was it? Now I understand what the TS/OP meant :-)

If the keys are identical on the two files, simplest for me is the join on the sequence numbers. Can verify that the keys are identical if desired.

Each of the three (if I have looked correctly) "333" keys on F1 will match with each of the three "333" keys on F2, giving nine "333" keys on the joined data.

If there is not a one-to-one relationship in the keys, then the easiest is to make the keys unique, by adding a sequence number with RESTART= for the key value field. So, two-part key, 333 001, 333 002, 333 003, and then the join will be one-for-one.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Sep 23, 2016 11:11 pm    Post subject:
Reply with quote

Happens sometimes. You're welcome, Bill. icon_smile.gif
Back to top
View user's profile Send private message
juares castro

New User


Joined: 04 May 2012
Posts: 18
Location: Brazil

PostPosted: Sat Sep 24, 2016 5:04 am    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

Sorry All!
The position of keys is 4 length 3.
My intention is understand why we have duplicated keys (or records) being created.
I am thinking my code is not OK... missing something.
In this example we have the same number of records but we could have this scenario with different numbers of records, and to demonstrate I filed with different content after the keys on 2nd file.

Thanks in advance!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Sat Sep 24, 2016 10:25 am    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

And what is it you want to know that hasn't been covered?
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Sat Sep 24, 2016 5:18 pm    Post subject: Re: Reply to: Joinkeys with duplicated keys
Reply with quote

juares castro wrote:
Sorry All!
The position of keys is 4 length 3.
My intention is understand why we have duplicated keys (or records) being created.
I am thinking my code is not OK... missing something.
In this example we have the same number of records but we could have this scenario with different numbers of records, and to demonstrate I filed with different content after the keys on 2nd file.

Thanks in advance!

1) All your questions have been clearly answered in previous messages

2) Based on your questions it seems you have ABSOLUTELY NO IDEA about JOIN operation. First, RTFM icon_exclaim.gif

3) You started with "I'm trying to do a joinkey in SYNCSORT..." but my understanding is you just want to compare two files, and detect matching/unmatching keys, isn't it? First of all, you need to clarify for yourself: what you need to do? and next - how you can do this. Not vice versa! icon_confused.gif
Back to top
View user's profile Send private message
juares castro

New User


Joined: 04 May 2012
Posts: 18
Location: Brazil

PostPosted: Sun Sep 25, 2016 2:10 am    Post subject:
Reply with quote

Hi again!
Sorry, but first of all, i hope to make questions in this forum i should not know everything about SYNCSORT. I am not an expert.
My intention is understand if there is a different code i have done that does not create records in SORTOUT. I mean, in my example we have the same number in 2 files ( 6 recs) with identical keys (but we could have different number of records with non identical keys) .
In this example my intention is create a SORTOUT with 6 records, as its entry files.
Looking the responses above i guess using a sequence number i could have the result i want.
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Sun Sep 25, 2016 3:19 am    Post subject:
Reply with quote

I repeat in bold:

First of all you have to understand what exactly you want (or you are supposed) to do?
Then you can ask about SYNCSORT, or not SYNCSORT, or any other tool

- from your message it's not obvious if SYNCSORT is appropriate tool in your case?
You did not explain what exactly your task is?
Back to top
View user's profile Send private message
juares castro

New User


Joined: 04 May 2012
Posts: 18
Location: Brazil

PostPosted: Mon Sep 26, 2016 6:15 pm    Post subject:
Reply with quote

Hi Sergeyken!
Repeating in normal text:
My intention is match two files and when we have duplicated keys in one of them or both the result not bring us "new" records.
I think SYNCSORT can do this, for sure i do not know how only using the keys to match. As i said maybe using a sequence number. Could i be right? (in bold)
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 65
Location: India

PostPosted: Mon Sep 26, 2016 9:45 pm    Post subject:
Reply with quote

Hello Juares,

Though same is already explained earlier in Mr. Woodger's post, will try putting it across again; *SORT (i.e. SYNC* or DF*) works by performing a cartesian join on keys. In your case for 3 bytes from position 4 achieved exactly this as shared in your first post. Since you had multiple matching entries ON THESE Key positions, hence a matched pair for all these records was also built by sort. Re-iterating - there are matching combinations of keys for 3 bytes from position 4.

To avoid this, you can have another key combination, i.e. 3 bytes from position 7, in conjunction to what you already have.
OR
You can use the suggestions others have already stated.

Again, the duplicates are not really duplicates, they are all 'unique combinations' if you compare them using the keys.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Sep 26, 2016 9:54 pm    Post subject:
Reply with quote

joinkeys with no repeated records This is precisely what you want, you could do yourself in future as there are a lot of examples and learning of this kind on this forum.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Tue Sep 27, 2016 1:05 am    Post subject: Reply to: Joinkeys with duplicated keys
Reply with quote

I'm probably going to take a knife to this topic...

Unless you are on a very old SyncSORT, a solution from 2009 is not appropriate.

Use JNF1CNTL and JNF2CNTL to append/prepend (fixed-length records/variable-length records) sequence numbers, then include those as minor keys for your JOINKEYS statements.

If this doesn't work, post, in full, what you have tried, including the full output (all three tasks) from the spool, with representative sample data, the output you obtain, and the output you desire.
Back to top
View user's profile Send private message
juares castro

New User


Joined: 04 May 2012
Posts: 18
Location: Brazil

PostPosted: Tue Sep 27, 2016 2:50 am    Post subject:
Reply with quote

I thank you all!
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 -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am
No new posts JOINKEYs and Variable files migusd SYNCSORT 8 Fri Jun 17, 2016 3:16 am
No new posts Too many edit macro's and too few PF ... don.leahy TSO/ISPF 7 Wed Feb 17, 2016 3:00 am


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