Page 1 of 1

JOIN UNPAIRED,F1,F2,ONLY

Posted: Fri Mar 23, 2012 6:40 pm
by tirupathi rao
HI

I have two fiels say F1 & F2

In F1 i have some records of lemght 1 to 40,
In F2 also i have some records of lemght 1 to 40,

Now i want to join the upaired records from F1 and F2

My code is like

SORT FIELDS=COPY
JOINKEYS FILE=F1,FIELDS=(1,40,A)
JOINKEYS FILE=F2,FIELDS=(1,40,A)
REFORMAT FIELDS=(F1:1,40,F2:1,40)
JOIN UNPAIRED,F1,F2,ONLY
OUTFIL FILES=01,BUILD=(01:1,40)

I am getting the all the records from F1 But i am not getting F2 records.
in the F2 records place i am getting SPACES.


In F1

1------------------40
SAHSHI 32450
NAVEN 32560
DEEPAK 32586

In F2

1--------------------40
THIRU 32553
DINESH 35686


In O/P
***********************TOP*********************************
1------------------40
SAHSHI 32450
NAVEN 32560
DEEPAK 32586

SPACES
***********************BOTTOM******************************

plz help me out to get the proper O/P.

Posted: Fri Mar 23, 2012 7:48 pm
by dbzTHEdinosauer
REFORMAT FIELDS=(F1:1,40,F2:1,40)

40 + 40 = 80


OUTFIL FILES=01,BUILD=(01:1,40)

40 = 40

Posted: Fri Mar 23, 2012 11:13 pm
by Frank Yaeger
plz help me out to get the proper O/P.
Well, we probably could if we knew what you meant by the proper o/p.
Now i want to join the upaired records from F1 and F2
This makes no sense. You can "join" paired records, but how can you join "unpaired records"? If I have AAA,BBB,CCC,EEEE in file1 and AAA,CCC,DDD,FFF in file2, I can join the paired records (AAA, CCC) on the key. But how can I join the unpaired records (BBB and EEE in file1, DDD and FFF in file2) - join which unpaired records in file1 to which unpaired records in file2?

Do you actually mean that you just want to keep the F1 records that don't have a match in F2, and keep the F2 records that don't have a match in F1? In your example, that would be ALL of the records since none of them match. A good example would show matching and unmatching records for F1 and F2 and the expected output.

If so, the correct control statements would be:

Code: Select all

  JOINKEYS FILE=F1,FIELDS=(1,40,A)           
  JOINKEYS FILE=F2,FIELDS=(1,40,A)           
  JOIN UNPAIRED,F1,F2,ONLY                   
  OPTION COPY    
  OUTFIL VTOF,BUILD=(5,40)                            
If that's not what you want to do, then you need to do a better job of explaining what you want to do exactly. Show an example of your input records and expected output records for all possible cases.

Posted: Sat Mar 24, 2012 12:39 pm
by tirupathi rao
HI Yaeger

i am sorry for the confusion,

In my F1 i have records like

********************************* Top of Data ********************
1---------------------------------40
EZP005 EZ0005 Y
EZP022 EZ0021 Y
EZP022 EZ0021 Y
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP770 EZ0143 Y
EZP770 EZ0143 Y
EZP163 EZ0170 Y
EZP220 EZ0220 Y
EZP194 EZ0250 Y
******************************** Bottom of Data *****************

In my F2 i have records like

********************************* Top of Data ********************
1----------------------------------40
EZP190 EZ0008 N
EZP200 EZ0205 N
EZP271 EZ0303 N
EZP273 EZ0304 N
EZP058 EZ1313A N
******************************** Bottom of Data *****************

There is no any matched records in F1 & F2 i know that(Bcz in 40 positon Y is there in F1 and N is there in F2)

Now i need to get all the records form F1 and F2. But i am getting only F1 Records not getting F2 records the OUTPUT which is iam getting is

********************************* Top of Data ********************
EZP005 EZ0005 Y
EZP022 EZ0021 Y
EZP022 EZ0021 Y
SPACESSSSSSSSSSSS
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP116 EZ0115 Y
EZP141 EZ0358 Y
EZP141 EZ0358 Y
EZP141 EZ0358 Y
EZP141 EZ0358 Y
EZP163 EZ0170 Y
SPACESSSSSSSSSSSSSS
EZP194 EZ0250 Y

These are only F1 records in place of F2 records i am getting SPACES.....

Posted: Mon Mar 26, 2012 10:57 pm
by Frank Yaeger
You didn't show the output you actually want, so I can only guess what that would be. My guess is that a DFSORT job like the following would do what you want:

Code: Select all

//S1 EXEC PGM=SORT                                                
//SYSOUT DD SYSOUT=*                                              
//SORTJNF1 DD DSN=...  input file (FB/40)             
//SORTJNF2 DD DSN=...  input file (FB/40)                            
//SORTOUT DD DSN=...  output file (FB/40)
//SYSIN DD *                                                      
  JOINKEYS FILE=F1,FIELDS=(1,40,A)                                
  JOINKEYS FILE=F2,FIELDS=(1,40,A)                                
  JOIN UNPAIRED,F1,F2,ONLY                                        
  OPTION COPY                                                     
  OUTFIL VTOF,BUILD=(5,40)                                        
For your input example, SORTOUT would have these records:

Code: Select all

EZP005            EZ0005               Y      
EZP022            EZ0021               Y      
EZP022            EZ0021               Y      
EZP058            EZ1313A              N      
EZP116            EZ0115               Y      
EZP116            EZ0115               Y      
EZP116            EZ0115               Y      
EZP116            EZ0115               Y      
EZP163            EZ0170               Y      
EZP190            EZ0008               N      
EZP194            EZ0250               Y      
EZP200            EZ0205               N      
EZP220            EZ0220               Y      
EZP271            EZ0303               N      
EZP273            EZ0304               N      
EZP770            EZ0143               Y      
EZP770            EZ0143               Y      
If that's not what you want for output, then you need to show exactly what you do want for output.

Getting error message while using Jointkeys

Posted: Wed Apr 03, 2013 10:36 pm
by manikandesvaran
Hi Frank,

am just using the below joinkey function

//IN1 DD DSN=FILE,DISP=SHR
//IN2 DD DSN=FILE1,DISP=SHR
//SORTOUT DD DSN=OUT,DISP=SHR
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,8,A)
JOINKEYS F2=IN2,FIELDS=(5,8,A)
JOIN UNPAIRED F1,F2
SORT FIELDS=COPY
/*


am getting error message as ICE404A 0 REFORMAT STATEMENT WAS REQUIRED, BUT NOT FOUND

If JOIN is speciified the REFORMAT filed is optional only.But still getting the error.

Could you please clarify ?

Posted: Wed Apr 03, 2013 11:56 pm
by NicC
Please do not tailgate old topics. Do not address posts to a particular person. You can wait a long time for Frank to answer as he retired months ago and no longer contributes to the forums. Do post in the correct part of the forum - you have a sort query - what is wrong with posting in the sort part of the forum? Finally, welcome to the forum.

Posted: Thu Apr 04, 2013 2:58 am
by William Collins
It is optional, but it is not your option whether or not to use it when necessary. You can always use it, there are cases where you don't need to, which is not your case. Use it.

Posted: Thu Apr 04, 2013 2:04 pm
by manikandesvaran
NicC wrote:Please do not tailgate old topics. Do not address posts to a particular person. You can wait a long time for Frank to answer as he retired months ago and no longer contributes to the forums. Do post in the correct part of the forum - you have a sort query - what is wrong with posting in the sort part of the forum? Finally, welcome to the forum.
Apologies for the mistake.Being a new person in this forum i am not sure about the rules.

Thanks for your kind.

Re: Getting error message while using Jointkeys

Posted: Fri Apr 05, 2013 1:23 pm
by Anuj Dhawan
manikandesvaran wrote:If JOIN is speciified the REFORMAT filed is optional only.But still getting the error.
That's not ture. The REFORMAT control statement is normally required if JOINKEYS is specified. It is optional if a JOIN control statement with the ONLY option has been specified in a join application since no records will actually be joined. So, as per your control statements, you'd need a REFORMAT as William has also said.

Re: Getting error message while using Jointkeys

Posted: Tue Apr 09, 2013 5:58 pm
by manikandesvaran
Anuj Dhawan wrote:
manikandesvaran wrote:If JOIN is speciified the REFORMAT filed is optional only.But still getting the error.
That's not ture. The REFORMAT control statement is normally required if JOINKEYS is specified. It is optional if a JOIN control statement with the ONLY option has been specified in a join application since no records will actually be joined. So, as per your control statements, you'd need a REFORMAT as William has also said.
Many thanks Anuj For the clarification.It was clear now for me.

Posted: Wed Apr 10, 2013 2:48 pm
by Anuj Dhawan
You're welcome.

Topic deleted by Admin

Posted: Mon Feb 01, 2016 10:24 pm
by academyindia4
<< Content deleted By Admin >>

Topic deleted by Admin

Posted: Mon Feb 01, 2016 10:27 pm
by academyindia4
<< Content deleted By Admin >>