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

Join records from 2 files with No Duplicates using DFSORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Sep 06, 2017 8:58 pm
Reply with quote

With your requirements, the options SORTED,NOSEQCK will not work as you wish.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Sep 06, 2017 10:06 pm
Reply with quote

If you don't care about the final order of records, and/or alignment of your fields/commas, then this approach must work
Code:
//SORTJOIN EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//MASTER   DD  *                                               
*---+----1----+----2----+----3----+----4----+----5----+----6   
0000L01455    ,XXX,YYY,ABCDEF,                                 
4814375       ,XXX,YYY,GHIJKLMNO,                             
4814375       ,   ,   ,PQRSTUV,                               
0000L01762    ,XXX,YYY,WXYZAB,                                 
0000L02594    ,XXX,YYY,CDEFGHIJKL,                             
4828156       ,XXX,YYY,MNOPQRST,                               
4828156       ,   ,   ,UVWXY,                                 
0000L02908    ,XXX,YYY,ZABCDEFGH,                             
2307914558    ,XXX,YYY,IOSIF W,                               
2307914558    ,XXX,YYY,IOSIF W,                               
//DETAILS  DD  *                                               
*---+----1----+----2----+----3----+----4----+----5----+----6   
0000L01455    ,2013,-2200.22,                                 
4814375       ,2013,-5122,                                     
0000L01762    ,2013,-100,                                     
0000L02594    ,2013,-1056,                                     
4828156       ,2013,-3369,         
0000L02908    ,2013,-2440,         
2307914558    ,2013,-62,           
2307914558    ,2013,-93,           
//SORTOUT  DD SYSOUT=*             
//JNF1CNTL DD *                   
 OMIT COND=(1,1,CH,EQ,C'*')       
 INREC IFTHEN=(WHEN=GROUP,         
               KEYBEGIN=(1,15),   
               PUSH=(81:SEQ=4))   
//JNF2CNTL DD *                   
 OMIT COND=(1,1,CH,EQ,C'*')       
 INREC IFTHEN=(WHEN=GROUP,         
               KEYBEGIN=(1,15),   
               PUSH=(81:SEQ=4))   
//SYSIN    DD *                   
 JOINKEYS F1=MASTER,               
          FIELDS=(01,15,A,         
                  81,4,A)         
 JOINKEYS F2=DETAILS,             
          FIELDS=(01,15,A,                       
                  81,4,A)                       
 JOIN UNPAIRED,F1                               
 REFORMAT FIELDS=(F1:1,14,                       
                  F1:15,30,                     
                  F2:15,30),                     
         FILL=C' '                               
 SORT FIELDS=COPY                               
 OUTREC BUILD=(1,74,SQZ=(SHIFT=LEFT,MID=C' '))   
 END                                             
//*                                             

The result is like this
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
0000L01455 ,XXX,YYY,ABCDEF, ,2013,-2200.22,                 
0000L01762 ,XXX,YYY,WXYZAB, ,2013,-100,                     
0000L02594 ,XXX,YYY,CDEFGHIJKL, ,2013,-1056,               
0000L02908 ,XXX,YYY,ZABCDEFGH, ,2013,-2440,                 
2307914558 ,XXX,YYY,IOSIF W, ,2013,-62,                     
2307914558 ,XXX,YYY,IOSIF W, ,2013,-93,                     
4814375 ,XXX,YYY,GHIJKLMNO, ,2013,-5122,                   
4814375 , , ,PQRSTUV,                                       
4828156 ,XXX,YYY,MNOPQRST, ,2013,-3369,                     
4828156 , , ,UVWXY,                                         
******************************** Bottom of Data ************

You can customize it as per additional rerquirements.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Oct 04, 2017 11:43 pm
Reply with quote

Hi Sergeyken,

Thanks a lot for your generous efforts in resolving my query. The below line in my output, it was just for representation purpose to show what values are coming at which column.

Code:

*---+----1----+----2----+----3----+----4----+----5----+----6


The control card which Magesh provided is working fine for my requirement. The only issue i am facing is that both the files are in unsorted order so when i am using the below code, it is only working till it is finding the key in sorted order. When it encounters the record whose key is not according to the sort, it is not joining the records.

I wanted to ask how can i modify the below sort card so that it can join the records when both the files are in unsorted order.

Code:
JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))


Please note that due to the nature of my requirement i can not write the output in sorted order.

Many Thanks !!
icon_smile.gif
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top