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
 

 

Replacing fields for Matching records using JOINKEYS

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

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Wed Jan 05, 2011 8:47 pm    Post subject: Replacing fields for Matching records using JOINKEYS
Reply with quote

Anuj Dhawan wrote:
You are welcome, not to worry about the typos.. icon_smile.gif

-Ad


Hello

I have a similar requirement and I used this sort cord.
But in the output I not am getting desired records.

JOINKEYS FILE=F1,FIELDS=(367,22,A,410,10,A)
JOINKEYS FILE=F2,FIELDS=(367,22,A,410,10,A)
SORT FIELDS=COPY
REFORMAT FIELDS=(F1:1,579,F2:580,8,F1:588,2)

File1 has 185 records
File2 has 4148 records

In the output I am getting 674 records where as I am expecting 185 records.

Requirement: File1 and File2 have length 589.
If key matches, replace (580,8) of file1 with (580,8) of file2 and write in another file.
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: Wed Jan 05, 2011 10:07 pm    Post subject:
Reply with quote

Hello,

Do not post the same question multiple times. The duplicate has been deleted.

Also, when you have a question, you should start a new topic for your question rather than post a reply to some inactive topic.

Which sort product is used on your system?
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Wed Jan 05, 2011 10:19 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Do not post the same question multiple times. The duplicate has been deleted.

Also, when you have a question, you should start a new topic for your question rather than post a reply to some inactive topic.

Which sort product is used on your system?


Sure, I will take care of this in future.
I am using DFSORT.
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: Thu Jan 06, 2011 2:28 am    Post subject:
Reply with quote

Well, without knowing what your input records look like, I can't tell you whether you should expect 185 records or not.

Do either of the files have duplicate records? Remember that with duplicate records, you can get multiple paired records for one input record, for example:

Input file1
A
A
A

Input file2
A
A
A
A

Although input file1 has 3 records and input file2 has 4 records, the output will have 12 records (cartesian join).

If you want more than speculation, you'd need to show a small sample of of input records and expected output where you don't get the number of output records you expect, so I could tell you whether you're expecting the right number or not.
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Thu Jan 06, 2011 5:39 pm    Post subject:
Reply with quote

Frank Yaeger wrote:
Well, without knowing what your input records look like, I can't tell you whether you should expect 185 records or not.

Do either of the files have duplicate records? Remember that with duplicate records, you can get multiple paired records for one input record, for example:

Input file1
A
A
A

Input file2
A
A
A
A

Although input file1 has 3 records and input file2 has 4 records, the output will have 12 records (cartesian join).

If you want more than speculation, you'd need to show a small sample of of input records and expected output where you don't get the number of output records you expect, so I could tell you whether you're expecting the right number or not.


Hello Frank

Example of my input files and output file

File 1
------
ABCD1234XYZ
ABCK3456XYZ
ABCD1234XYZ
ABCP1234000

File 2
------
ABCD9876XYZ
ABCD9876XYZ
ABCK1234XYZ
ABCD1234XYZ
ABCD1234XYZ
ABCN1234XYZ

Required Output
---------------
Match file1(1,4,CH,A,9,3,CH,A) with file2(1,4,CH,A,9,3,CH,A)
If matches Write into File3

File 3
------
ABCD9876XYZ
ABCK1234XYZ
ABCD9876XYZ
ABCP1234000

Thanks
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jan 06, 2011 6:51 pm    Post subject:
Reply with quote

Binaya -- The original thread was for SyncSort, so I also encourage you to SUBmit the below Job and post us back with the SYSOUT mesages, please:
Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*    <--- look at messages
//SORTIN DD *
//SORTOUT DD DUMMY
//SYSIN DD *
  SORT FIELDS=COPY
/* 
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: Thu Jan 06, 2011 11:46 pm    Post subject:
Reply with quote

Your example makes no sense. I can't figure out what you want.

For example, ABCP1234000 in file1 has no match in file2, but you show it in file3. You have multiple instances of ABCDxxx in both files, but I can't figure out what "rules" you're using to get the output records in these cases.

You need to do a much better job of explaining the "rules" for what you want to do with a good example before I can help you.
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Fri Jan 07, 2011 7:02 pm    Post subject:
Reply with quote

Frank Yaeger wrote:
Your example makes no sense. I can't figure out what you want.

For example, ABCP1234000 in file1 has no match in file2, but you show it in file3. You have multiple instances of ABCDxxx in both files, but I can't figure out what "rules" you're using to get the output records in these cases.

You need to do a much better job of explaining the "rules" for what you want to do with a good example before I can help you.


Frank

Sorry for the confusion.
Here is the data and my requirement.

File 1
------
ABCD0000XYZ
ABCD0000XYZ
ABCK0000XYZ
ABCP0000PPP

File 2
------
ABCD9876XYZ
ABCD9876XYZ
ABCD9876XYZ
ABCK1234XYZ

File1 and File2 have duplicate records.

Required Output : File 3
----------------------------
ABCD9876XYZ--->File 1 Key Matched with File 2 key
ABCD9876XYZ--->File 1 Key Matched with File 2 key
ABCK1234XYZ--->File 1 Key Matched with File 2 key
ABCP0000PPP--->File 1 Key Unmatched with File 2 key

Thanks
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon Jan 10, 2011 11:19 pm    Post subject:
Reply with quote

Binaya,

Looks like you want to perform a one-to-one match within the duplicates. If that is indeed true then the following DFSORT JCL will give you the desired results. I also assumed that your input is already sorted on the keys.

Code:

//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD *                                         
ABCD0000XYZ                                             
ABCD0000XYZ                                             
ABCK0000XYZ                                             
ABCP0000PPP                                             
//INB      DD *                                         
ABCD9876XYZ                                             
ABCD9876XYZ                                             
ABCD9876XYZ                                             
ABCK1234XYZ                                             
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(81,15,A),SORTED,NOSEQCK       
  JOINKEYS F2=INB,FIELDS=(81,15,A),SORTED,NOSEQCK       
  JOIN UNPAIRED                                         
  REFORMAT FIELDS=(F1:1,80,?,F2:1,80)                   
  OUTFIL IFOUTLEN=80,INCLUDE=(81,1,SS,EQ,C'B,1'),       
  IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),BUILD=(82,80))         
//*                                                     
//JNF1CNTL DD *                                         
  INREC OVERLAY=(81:1,4,9,3,SEQNUM,8,ZD,RESTART=(81,7))
//JNF2CNTL DD *                                         
  INREC OVERLAY=(81:1,4,9,3,SEQNUM,8,ZD,RESTART=(81,7))
//*
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 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 DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm
No new posts Replacing same string with different ... vickey_dw DFSORT/ICETOOL 6 Wed Feb 22, 2017 10:44 pm


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