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
 

 

How to match two fields with SORT JOINKEYS !

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: How to match two fields with SORT JOINKEYS !
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    Post subject: sorry.......corrected the mistake.
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    Post subject: Reply to: How to match two fields with SORT JOINKEYS !
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

Site Director


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

PostPosted: Mon Jan 31, 2011 9:43 pm    Post subject:
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: 2284
Location: @my desk

PostPosted: Tue Feb 08, 2011 8:19 pm    Post subject:
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: 2284
Location: @my desk

PostPosted: Wed Feb 09, 2011 8:08 pm    Post subject:
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    Post subject: Reply to: How to match two fields with SORT JOINKEYS !
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: 2284
Location: @my desk

PostPosted: Fri Feb 25, 2011 10:30 am    Post subject:
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    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 SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm


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