IBM Mainframe Forum

by **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

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

search the forum and the sibling forum http://ibmmainframes.com
for cartesian product joinkeys

by **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

by **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

by **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

by **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):

Select all

//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:

Select all

ABCD123456
ABCD234567
BCDE123456
BCDE234567
CDEF123456
CDEF234567
 

Hope this helps.

by **mainframenoob** » Thu Jul 07, 2016 12:51 pm

Great. Thank you very much

IBM Mainframe Forum

Join 2 files records

Join 2 files records

Re: Join 2 files records

Re: Join 2 files records

Re: Join 2 files records

Re: Join 2 files records

Re: Join 2 files records

Re: Join 2 files records