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
 

 

Problem with joinkeys

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

New User


Joined: 09 Nov 2006
Posts: 21

PostPosted: Thu Apr 26, 2012 2:43 pm    Post subject: Problem with joinkeys
Reply with quote

Hi,

My requirement is as below

i have two files
file 1

Code:
subscription    status order no
000097636347KK6414656
000097636347KK6416152
000097641712KK6414657
000097641712KK6416153
000097673341   0000000
000097673462KK6415711
000097673462KK6417062



File 2

Code:
CON0000000  XXX00009763634700000000000
ASC0000000   XXX00009763634700000000000
CON0000000  XXX00009764171200000000000
ASC0000000   XXX00009764171200000000000
CON0000000  XXX00009767334100000000000
ASC0000000   XXX00009767334100000000000


While using join keys
I picking up only order no's from first file(example:6416152)
and placing it as below in file 2

Code:
CON6414656  XXX00009763634700000000000
ASC6416152   XXX00009763634700000000000
CON6414657  XXX00009764171200000000000
ASC6416153   XXX00009764171200000000000
CON0000000  XXX00009767334100000000000
[b]ASC6415711   XXX00009767334100000000000[/b]
CON0000000  XXX00009767346200000000000


The problem is for the number 000097673341 there is only one record in file 1 but while using join keys the order no 6415711 which is actually for 000097673462 will be placed for 000097673341.But actually the order 6415711 should have been placed for 000097673462.

is there any better method to do this so that if the numbers dont match then simply write the record. with out the order no then write the correct order no to correct subscription.


The join key used is

Code:
//SYSIN   DD *                                                         
 JOINKEYS F1=IN1,FIELDS=(141,8,A),SORTED,NOSEQCK                       
 JOINKEYS F2=IN2,FIELDS=(22,8,A),SORTED,NOSEQCK                         
 REFORMAT FIELDS=(F1:1,140,F2:1,21)                                     
 OPTION COPY                                                           
 OUTREC FIELDS=(1:1,45,155,7,153,2,55,95)                               
/*                                                                     
//*                                                                     
//JNF1CNTL DD *                                                         
 INREC OVERLAY=(141:SEQNUM,8,BI)                                       
/*                                                                     
//JNF2CNTL DD *                                                         
 INREC OVERLAY=(22:SEQNUM,8,BI)

File 1 is 21 bytes and file 2 is 140 bytes
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Thu Apr 26, 2012 2:48 pm    Post subject: Reply to: joinkeys
Reply with quote

If you are using a sequence number for joining it will always create a one-for-one match between the two files based solely on record order. This technique if for when there is no key that can match between the two files, and such a physical relationship exists.

Since you seem to have the same key on both files, why don't you use the key?
Back to top
View user's profile Send private message
mvmadhavi

New User


Joined: 09 Nov 2006
Posts: 21

PostPosted: Thu Apr 26, 2012 2:53 pm    Post subject:
Reply with quote

But the key (subscription) could be duplicates as we see in the files

CON6414656 XXX00009763634700000000000
ASC6416152 XXX00009763634700000000000
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Thu Apr 26, 2012 3:11 pm    Post subject: Reply to: joinkeys
Reply with quote

So you have to deal with it with some code.

If you have the duplicate keys, what matching do you want to take place?

Can there be duplicates on both files?
Back to top
View user's profile Send private message
mvmadhavi

New User


Joined: 09 Nov 2006
Posts: 21

PostPosted: Thu Apr 26, 2012 3:36 pm    Post subject:
Reply with quote

even though keys are duplicate matching has to be on subscripton.
there could be duplicates on both files
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Apr 26, 2012 10:23 pm    Post subject:
Reply with quote

mvmadhavi,

It is not clear as to how the matching is supposed to be done.

Code:

000097636347 KK 6414656
000097636347 KK 6416152


You have 2 records for the subscription 000097636347 in both files.

Code:

CON0000000  XXX 000097636347 00000000000
ASC0000000  XXX 000097636347 00000000000



Now what is the expected output from the above 2 records?

Is it
Code:

CON0000000  XXX 000097636347 00000000000 6414656
ASC0000000  XXX 000097636347 00000000000 6416152


or

Since you have duplicates in both files do you generate a Cartesian join?

Code:

CON0000000  XXX 000097636347 00000000000 6414656
ASC0000000  XXX 000097636347 00000000000 6414656

CON0000000  XXX 000097636347 00000000000 6416152
ASC0000000  XXX 000097636347 00000000000 6416152

Back to top
View user's profile Send private message
mvmadhavi

New User


Joined: 09 Nov 2006
Posts: 21

PostPosted: Fri Apr 27, 2012 11:28 am    Post subject:
Reply with quote

Hi,

I have checked the cartesian join. this is not the result expected.

Expected is

File 1

000097636347FB6414656
000097636347FB6416152
000097641712FB6414657
000097641712FB6416153
000097673341 0000000
000097673462FB6415711
000097673462FB6417062



File 2

CON0000000 XXX00009763634700000000000
ASC0000000 XXX00009763634700000000000
CON0000000 XXX00009764171200000000000
ASC0000000 XXX00009764171200000000000
CON0000000 XXX00009767334100000000000
ASC0000000 XXX00009767334100000000000

CON0000000 XXX00009767346200000000000
ASC0000000 XXX00009767346200000000000



Expected

CON6414656 EAB00009763634700000000000
ASC6416152 EAB00009763634700000000000
CON6414657 EAB00009764171200000000000
ASC6416153 EAB00009764171200000000000
CON0000000 EAB00009767334100000000000
ASC0000000 EAB00009767334100000000000

CON6415711 EAB00009767346200000000000
ASC6417062 EAB00009767346200000000000
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: Fri Apr 27, 2012 8:05 pm    Post subject:
Reply with quote

Hello,

In addition to the sample input/output, you need to post the "rules" for processing. The rules should be presented in paragra[ph/sentence form.
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 Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm


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