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 

Join of 2 files with some specific conditions.

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


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Mon Aug 08, 2011 9:57 am    Post subject: Join of 2 files with some specific conditions. Reply with quote
I have 2 input files (same lenght and same fields) and and need to use SORT to join the files using these conditions:

a- when the record exists in file 1 but not in file 2, I want to output the record from file 1 including a letter D at the end.

b- when the record exists in file 2 but not in file 1, I want to output the record from file 2 including a letter C at the end.

c- when the record exists in both files 1 and 2, I want to output the record from file 2 including a letter C at the end.

Can anyone help me with this?
thanks
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Mon Aug 08, 2011 10:31 am    Post subject: Reply with quote
prog_mario,

For case b & C you want the same letter at the end? Assuming your input file lrecl is 80, recfm of FB and the key to match records is in pos 1 for a length of 5, the following DFSORT JCL will give you the desired results. The indicator is at the 81'st position.

Code:

//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD *                                       
11111  - I EXIST ONLY IN FILE 1                       
22222  - BOTH FILES HAS ME                             
//INB      DD *                                       
22222  - BOTH FILES HAS ME                             
33333  - I EXIST ONLY IN FILE 2                       
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                       
  OPTION COPY                                         
  JOINKEYS F1=INA,FIELDS=(1,5,A)                       
  JOINKEYS F2=INB,FIELDS=(1,5,A)                       
  JOIN UNPAIRED                                       
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)                 
  INREC IFOUTLEN=81,                                   
  IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80,C'D')), 
  IFTHEN=(WHEN=(161,1,SS,EQ,C'2,B'),BUILD=(81,80,C'C'))
//*

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


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Mon Aug 08, 2011 11:04 am    Post subject: Reply with quote
It's not working because when I run the job it seems that Syncsort is executed.
Code:

SYNCSORT LICENSED FOR CPU SERIAL NUMBER 98B8A,
SYSIN :                                       
  OPTION COPY                                 
  JOINKEYS F1=INA,FIELDS=(13,05,A,06,07,A)     
           *                                   
  JOINKEYS F2=INB,FIELDS=(13,05,A,06,07,A)     
           *                                   
  JOIN UNPAIRED                               
  REFORMAT FIELDS=(F1:01,21,                   
                   F2:01,21,?)                 
                           *                   
  OUTREC IFOUTLEN=22,                         
         IFTHEN=(WHEN=(43,1,CH,EQ,C'1'),       
            BUILD=(01,21,C'D')),               
         IFTHEN=(WHEN=(43,1,SS,EQ,C'2,B'),     
            BUILD=(22,21,C'C'))               
WER268A  JOINKEYS STATEMENT: SYNTAX ERROR

_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Mon Aug 08, 2011 11:06 am    Post subject: Reply with quote
This is my sort card:
Code:

  OPTION COPY                             
  JOINKEYS F1=INA,FIELDS=(13,05,A,06,07,A)
  JOINKEYS F2=INB,FIELDS=(13,05,A,06,07,A)
  JOIN UNPAIRED                           
  REFORMAT FIELDS=(F1:01,21,             
                   F2:01,21,?)           
  OUTREC IFOUTLEN=22,                     
         IFTHEN=(WHEN=(43,1,CH,EQ,C'1'), 
            BUILD=(01,21,C'D')),         
         IFTHEN=(WHEN=(43,1,SS,EQ,C'2,B'),
            BUILD=(22,21,C'C'))

_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Mon Aug 08, 2011 11:19 am    Post subject: Reply with quote
prog_mario,

The Job works fine with DFSORT. Your Error messages indicate you are using syncsort. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
papadi
Supermod


Joined: 20 Oct 2009
Posts: 594
Topics: 1
PostPosted: Mon Aug 08, 2011 11:33 am    Post subject: Reply with quote
While the same basic process will work with both sorts, you must follow the syntax rules for each product. . .

I don't have Syncsort reference material with me, but the problem you are having is somethng you should readily see if you look in the Syncsort documentation. If you don't have the documentation and your organization is licensed to use the product, you may get the material for free from Syncsort support.
_________________
All the best,

di
Back to top
View user's profile Send private message
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Mon Aug 08, 2011 11:46 am    Post subject: Reply with quote
It's working this way:
Code:

  JOINKEYS FILE=F1,FIELDS=(008,005,A,001,007,A)
  JOINKEYS FILE=F2,FIELDS=(008,005,A,001,007,A)
  JOIN UNPAIRED                               
  REFORMAT FIELDS=(F1:01,21,                   
                   F2:01,21)                   
  OUTREC IFOUTLEN=22,                         
         IFTHEN=(WHEN=(01,21,CH,EQ,22,21,CH), 
            BUILD=(22,21,C'C')),               
         IFTHEN=(WHEN=(01,21,CH,LT,22,21,CH), 
            BUILD=(22,21,C'C')),               
         IFTHEN=(WHEN=(01,21,CH,GT,22,21,CH), 
            BUILD=(01,21,C'D'))               
  SORT FIELDS=COPY

_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Aug 08, 2011 12:30 pm    Post subject: Reply with quote
I could be wrong but Syncsort doesn't support use of ?. You may try something like below (Untested)...

Code:
//SYSIN    DD *                                           
 OPTION COPY                                             
 JOINKEYS FILE=F1,FIELDS=(008,005,A,001,007,A)           
 JOINKEYS FILE=F2,FIELDS=(008,005,A,001,007,A)           
 JOIN UNPAIRED                                           
 REFORMAT FIELDS=(F1:01,21,F2:01,21),FILL=C' '              OR USE --> FILL=(X'40')         
 INREC IFOUTLEN=22,                                       
   IFTHEN=(WHEN=(22,21,CH,GT,C' '),BUILD=(22,21,C'D')),   
   IFTHEN=(WHEN=NONE,BUILD=(01,21,C'C'))                 
//*

Thanks,
Back to top
View user's profile Send private message
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