Join 2 files records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Join 2 files records

Postby mainframenoob » Wed Jul 06, 2016 2:48 pm

Hi Friends,

I have the below requirement. There is no key field in the file A & B. Can I use JOIN to achieve this. Just some pointer will do. Please help

File A - record length 4

ABCD
BCDE
CDEF

File B - record length 6
123456
234567

I want the output like this

File C - record length 10
ABCD123456
ABCD234567
BCDE123456
BCDE234567
CDEF123456
CDEF234567
mainframenoob
 
Posts: 4
Joined: Wed Jul 06, 2016 2:39 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Join 2 files records

Postby enrico-sorichetti » Wed Jul 06, 2016 8:29 pm

search the forum and the sibling forum http://ibmmainframes.com
for cartesian product joinkeys
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
mainframenoob (Wed Jul 06, 2016 9:11 pm)
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Join 2 files records

Postby mainframenoob » Wed Jul 06, 2016 8:55 pm

Hi Enrico,

Thanks for your reply. I tried searching already. I could not find any examples with no common field in file A & File B. All join examples works on a common field in both file A & B
mainframenoob
 
Posts: 4
Joined: Wed Jul 06, 2016 2:39 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Join 2 files records

Postby enrico-sorichetti » Wed Jul 06, 2016 9:54 pm

just add a sequence number , use it as the key for the join and reformat the output accordingly,
I am pretty sure that at least one example is there :)
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Join 2 files records

Postby mainframenoob » Thu Jul 07, 2016 8:30 am

I could not find any examples suitable for me. I did like below. I am not sure whether this is the most efficient method. But it worked for me. Please suggest if there is better way. Thanks for your help.

File A - record length 4
ABCD
BCDE
CDEF

File B - record length 6
123456
234567

repeated and reformatted File A data as below- record length 5
ABCD1
BCDE2
CDEF3
ABCD4
BCDE5
CDEF6

repeated and reformatted File B data as below- record length 7
1234561
1234562
1234563
2345674
2345675
2345676

performed an inner join on the sequence number(column 5 on file-A & coulmn 7 on file-B) and got the output as below.

File C - record length 10
ABCD123456
ABCD234567
BCDE123456
BCDE234567
CDEF123456
CDEF234567
mainframenoob
 
Posts: 4
Joined: Wed Jul 06, 2016 2:39 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Join 2 files records

Postby Aki88 » Thu Jul 07, 2016 12:36 pm

Hello,

If cartesian-join is what you're really looking for, guess this should also do the trick (which is in-line with what you've already done); the method used skips the repetitions, instead it employs the basic JOIN functionality of DFSORT to get what is needed (which I must say in some situations causes trouble for some and requires extra logic to handle data - think JOIN when one has duplicate records):


//STEP001  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*  
//SORTJNF1 DD *        
ABCD                    
BCDE                    
CDEF                    
/*                      
//SORTJNF2 DD *        
123456                  
234567                  
/*                      
//*                    
//SORTOUT  DD SYSOUT=*  
//*                    
//JNF1CNTL DD *        
 INREC BUILD=(1,4,C'X')
/*                      
//JNF2CNTL DD *        
 INREC BUILD=(1,6,C'X')
/*                      
//*                                
//SYSIN    DD *                    
 JOINKEYS FILES=F1,FIELDS=(5,1,A)  
 JOINKEYS FILES=F2,FIELDS=(7,1,A)  
 REFORMAT FIELDS=(F1:1,5,F2:1,7,?)
 SORT FIELDS=COPY                  
 OUTFIL BUILD=(1,4,6,6)            
/*                                
 


SORTOUT:


ABCD123456
ABCD234567
BCDE123456
BCDE234567
CDEF123456
CDEF234567
 


Hope this helps.

These users thanked the author Aki88 for the post:
mainframenoob (Thu Jul 07, 2016 12:51 pm)
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Join 2 files records

Postby mainframenoob » Thu Jul 07, 2016 12:51 pm

Great. Thank you very much
mainframenoob
 
Posts: 4
Joined: Wed Jul 06, 2016 2:39 pm
Has thanked: 2 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post