MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joinkeys - Can it be conditional?

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
mangsk
Beginner


Joined: 05 Jun 2004
Posts: 46
Topics: 18
PostPosted: Mon Aug 15, 2011 10:28 am    Post subject: Joinkeys - Can it be conditional? Reply with quote
I have 2 files
File F1:
Code:

103ABCDEF103XXXXXX
104XYZABC104YYYYYY
104BNDRFD104HHFFHH
105RDFHHD105HHFHHF
109YYRYYR109NMJFNN

File F2:
Code:

10301
10402
10503
10602
10703


2nd file contains the number of the outfile (byte 4 and 5) where the record from 1st file should go to. e.g. The record having 1st 3 bytes as 103 will find a match on file 2, then it will go to outfile '01' , the 105 will go to '03' and likewise. The 109 will go to outfile 03 because it does not find a match on File 2.
I wrote the JOINKEYS and it works -
Code:

JOINKEYS FILES=F1,FIELDS=(1,03,A)     
JOINKEYS FILES=F2,FIELDS=(1,03,A)     
REFORMAT FIELDS=(F1:1,18,F2:4,2)     
SORT FIELDS=(1,18,CH,A)               
JOIN UNPAIRED,F1                       
OUTFIL FILES=01,                       
        INCLUDE=(19,02,CH,EQ,C'01')   
OUTFIL FILES=02,                       
        INCLUDE=(19,02,CH,EQ,C'02')   
OUTFIL FILES=03,                       
        INCLUDE=(19,02,CH,EQ,C'03',OR,
                 19,02,CH,EQ,C'  ')   


Now, I came to know that the File1 may have 1st 3 chars blank. If they are blank, then I have to check the bytes 10 thru 12.
So, my new File 1 is
Code:

103ABCDEF103XXXXXX
104XYZABC104YYYYYY
104BNDRFD104HHFFHH
105RDFHHD105HHFHHF
109YYRYYR109NMJFNN
   TTRBRH106JJFJFJ
   YYRYYR107NMJFNN

Now, can the JOINKEYS be changed for conditional check ? The condition being, if 1st 3 bytes are blank, then check for bytes 10 to 12 and join it with the 1st 3 bytes from file 2?
It can be done in 2 steps. But can this be done in one step?
Thanks.
_________________
Regards,
Mangsk
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Aug 15, 2011 10:42 am    Post subject: Reply with quote
mangsk,

Use the following DFSORT JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//SORTJNF1 DD *                                     
103ABCDEF103XXXXXX                                 
104XYZABC104YYYYYY                                 
104BNDRFD104HHFFHH                                 
105RDFHHD105HHFHHF                                 
109YYRYYR109NMJFNN                                 
   TTRBRH106JJFJFJ                                 
   YYRYYR107NMJFNN                                 
//SORTJNF2 DD *                                     
10301                                               
10402                                               
10503                                               
10602                                               
10703                                               
//SORTOF01 DD SYSOUT=*                             
//SORTOF02 DD SYSOUT=*                             
//SORTOF03 DD SYSOUT=*                             
//SYSIN    DD *                                     
  JOINKEYS FILES=F1,FIELDS=(19,3,A)                 
  JOINKEYS FILES=F2,FIELDS=(01,3,A)                 
  JOIN UNPAIRED,F1                                 
  REFORMAT FIELDS=(F1:1,18,F2:4,2)   
  SORT FIELDS=(1,18,CH,A)                 
                                                   
  OUTFIL FILES=01,INCLUDE=(19,02,CH,EQ,C'01')       
  OUTFIL FILES=02,INCLUDE=(19,02,CH,EQ,C'02')       
  OUTFIL FILES=03,SAVE                             
//JNF1CNTL DD *                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(19:1,3)),       
  IFTHEN=(WHEN=(1,3,CH,EQ,C' '),OVERLAY=(19:10,3)) 
//*


The output from this job is

SORTOF01
Code:

103ABCDEF103XXXXXX01                                                 

SORTOF02
Code:

   TTRBRH106JJFJFJ02
104BNDRFD104HHFFHH02
104XYZABC104YYYYYY02

SORTOF03
Code:

   YYRYYR107NMJFNN03
105RDFHHD105HHFHHF03
109YYRYYR109NMJFNN   

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Aug 15, 2011 11:04 am    Post subject: Reply with quote
mangsk,
Ooops..too late... but similar approach.See if below works...
Code:
//SYSIN DD *                                                   
  JOINKEYS FILE=F1,FIELDS=(81,03,A)                             
  JOINKEYS FILE=F2,FIELDS=(01,03,A)                             
  JOIN UNPAIRED,F1                                             
  REFORMAT FIELDS=(F1:1,18,F2:4,2)                             
  SORT FIELDS=(1,18,CH,A)                                       
  OUTFIL FNAMES=01,                                         
          INCLUDE=(19,02,CH,EQ,C'01')                           
  OUTFIL FNAMES=02,                                         
          INCLUDE=(19,02,CH,EQ,C'02')                           
  OUTFIL FNAMES=03,                                         
          INCLUDE=(19,02,CH,EQ,C'03',OR,                       
                   19,02,CH,EQ,C'  ')                           
/*                                                             
//JNF1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:1,3,10,3,               
                                   81:81,6,SQZ=(SHIFT=LEFT)))
/*                                                           

Thanks,
Back to top
View user's profile Send private message
mangsk
Beginner


Joined: 05 Jun 2004
Posts: 46
Topics: 18
PostPosted: Mon Aug 15, 2011 12:21 pm    Post subject: Reply with quote
kolusu, Sqlcode,
Thank you for your reply.
Unfortunately, my shop has Syncsort 1.3.2.1 and it doesn't seem to support the JNF1CNTL statement. I get return code of 0; but I don't get the desired output.
I believe Kolusu does not help with Syncsort. Sad
Can anyone else help out on this for Syncsort? Maybe Alissa?
_________________
Regards,
Mangsk
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Aug 15, 2011 12:29 pm    Post subject: Reply with quote
mangsk,
Actually, I shoud have asked this earlier but what is the LRECL and RECFM for both the input files.

Edited:- Syncsort doesn't support JNF*CNTL. If your input files are of different LRECL, you would need separate step which would mimic JNF1CNTL processing. If they are of same length,please let us know.

Thanks,
Back to top
View user's profile Send private message
mangsk
Beginner


Joined: 05 Jun 2004
Posts: 46
Topics: 18
PostPosted: Mon Aug 15, 2011 1:09 pm    Post subject: Reply with quote
Sqlcode,
My files are of different length. File2 is actually 5 bytes while the File1 is bigger than 18 bytes I have here in the example.
So, with reference to your updated response, with SYNCSORT it seems there is no other way other than going with 2 step process.
Sad
Thank you for all your help!
_________________
Regards,
Mangsk
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group