IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to match two fields with SORT JOINKEYS !
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Jan 31, 2011 3:31 pm
 Reply with quote
Hi,

I have an input file (LRECL=26, RECFM=FB) with two key fileds.

File-1:
Code:

----+----1----+----2----+----3
    ORDER1 0987654 0912345
    ORDER2 0876543 0876543
    ORDER3 0123456 0654321


I have another input file (LRECL=80, RECFM=FB) with one key filed.

File-2:
Code:

----+----1----+----2----+----3----+----4----+----5
0987654 HELLO BEAUTY PARLOUR 1ST STREET BANGALORE
0912345 SIVA PHARMACY        ROUND BLDG HYDERABAD
0876543 FIRADAUS COMPANY     SEVENTH ST CHENNAI
0876543 MUKTHA TRADERS       MUKTHAS    CHENNAI
0123456 DAVID BROTHERS       GOLD BRICK BANGALORE
0654321 RAMA BOOK HOUSE      CENTRE ST  TRIVANDRUM


I want to match two fields (12th column and 20th column) from file-1 with field (1st column) in file-2 and generate a output as shown below:

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2
    ORDER1 0987654 HELLO BEAUTY PARLOUR 1ST STREET BANGALORE 0912345 SIVA PHARMACY        ROUND BLDG HYDERABAD
    ORDER2 0876543 FIRADAUS COMPANY     SEVENTH ST CHENNAI   0876543 MUKTHA TRADERS       MUKTHAS    CHENNAI
    ORDER3 0123456 DAVID BROTHERS       GOLD BRICK BANGALORE 0654321 RAMA BOOK HOUSE      CENTRE ST  TRIVANDRUM


Please help me if this is achievable with SORT JOINKEYS. I have tried but able match it only on one field !

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

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Jan 31, 2011 3:38 pm
 Reply with quote
Hi, I am sorry for the typo.

Content in File-2:
Code:

----+----1----+----2----+----3----+----4----+----5
0987654 HELLO BEAUTY PARLOUR 1ST STREET BANGALORE
0912345 SIVA PHARMACY        ROUND BLDG HYDERABAD
0876543 FIRADAUS COMPANY     SEVENTH ST CHENNAI
0123456 DAVID BROTHERS       GOLD BRICK BANGALORE
0654321 RAMA BOOK HOUSE      CENTRE ST  TRIVANDRUM


No changes to File-1:
Code:

----+----1----+----2----+----3
    ORDER1 0987654 0912345
    ORDER2 0876543 0876543
    ORDER3 0123456 0654321


Expected Output:
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2
    ORDER1 0987654 HELLO BEAUTY PARLOUR 1ST STREET BANGALORE 0912345 SIVA PHARMACY        ROUND BLDG HYDERABAD
    ORDER2 0876543 FIRADAUS COMPANY     SEVENTH ST CHENNAI   0876543 FIRADAUS COMPANY     SEVENTH ST CHENNAI
    ORDER3 0123456 DAVID BROTHERS       GOLD BRICK BANGALORE 0654321 RAMA BOOK HOUSE      CENTRE ST  TRIVANDRUM


The file-2 will have only single entry and no duplicates.

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

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Jan 31, 2011 5:01 pm
 Reply with quote
The File-2 will always have the matching keys for File-1. I've tried multiple keys in FIELDS but it works like AND condition. So, it omitted 1st and 3rd records but written 2nd record alone to output.

Please help.

Thanks.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon Jan 31, 2011 9:43 pm
 Reply with quote
Hello,

Why have you not posted the jcl and sort control statements used?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Tue Feb 08, 2011 8:19 pm
 Reply with quote
Ramsri,

Will there be always 2 records in file-2 per ORDER?
Will 2 consecutive records in file-2 always belong to the same ORDER?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Feb 09, 2011 8:08 pm
 Reply with quote
Ramsri,

I missed the point that you will not be having duplicates in file-2.

You might want to give the below SYNCTOOL job a try.
Code:
//JS10     EXEC PGM=SYNCTOOL                                       
//TOOLMSG  DD  SYSOUT=*                                           
//DFSMSG   DD  SYSOUT=*                                           
//IN1      DD  DSN= input file-1  (FB/26)
//CTL2JNF1 DD  DSN=&T1,DISP=(,PASS),UNIT=SYSDA                     
//CTL2JNF2 DD  DSN= input file-2  (FB/80)
//OUT      DD  DSN= output file   (FB/200)
//TOOLIN   DD  *                                                   
  COPY FROM(IN1)      TO(CTL2JNF1) USING(CTL1)                     
  SORT FROM(CTL2JNF1) TO(OUT)      USING(CTL2)
//CTL1CNTL DD  *                                                   
  OUTFIL BUILD=(12,7,1,10,C'1',/,20,7,1,10,C'2')
//CTL2CNTL DD  *                                                   
  JOINKEYS FILE=F1,FIELDS=(1,7,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,7,A)                                   
  REFORMAT FIELDS=(F1:8,11,F2:1,80)                                 
  SORT FIELDS=(1,11,CH,A)
  OUTREC IFTHEN=(WHEN=GROUP,RECORDS=2,PUSH=(92:12,80))   
  OUTFIL INCLUDE=(11,1,ZD,EQ,2),BUILD=(1,10,X,92,50,X,12,50,200:X)
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Tue Feb 22, 2011 2:50 pm
 Reply with quote
Hi Arun Raj,

Wonderful........It has produced me the expected result. Thank you very much for your effort and saving my day.

Thanks a lot once again.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Fri Feb 25, 2011 10:30 am
 Reply with quote
Ramsri,

Glad that I could help. You're welcome.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us