JOIN UNPAIRED,F1,F2,ONLY
Moderators: Frank Yaeger, DikDude, Moderator Group
-
- Member
- Posts: 4
- Joined: Thu Feb 16, 2012 5:03 pm
JOIN UNPAIRED,F1,F2,ONLY
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.
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.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Well, we probably could if we knew what you meant by the proper o/p.plz help me out to get the proper O/P.
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?Now i want to join the upaired records from F1 and F2
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)
Last edited by Frank Yaeger on Mon Mar 26, 2012 10:54 pm, edited 1 time in total.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
-
- Member
- Posts: 4
- Joined: Thu Feb 16, 2012 5:03 pm
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.....
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.....
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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:
For your input example, SORTOUT would have these records:
If that's not what you want for output, then you need to show exactly what you do want for output.
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)
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
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
-
- Member
- Posts: 5
- Joined: Wed Apr 03, 2013 10:28 pm
Getting error message while using Jointkeys
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 ?
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 ?
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.
Regards
Nic
Nic
-
- Active Member
- Posts: 732
- Joined: Thu May 24, 2012 4:07 am
-
- Member
- Posts: 5
- Joined: Wed Apr 03, 2013 10:28 pm
Apologies for the mistake.Being a new person in this forum i am not sure about the rules.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.
Thanks for your kind.
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
Re: Getting error message while using Jointkeys
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.manikandesvaran wrote:If JOIN is speciified the REFORMAT filed is optional only.But still getting the error.
Regards,
Anuj
Anuj
-
- Member
- Posts: 5
- Joined: Wed Apr 03, 2013 10:28 pm
Re: Getting error message while using Jointkeys
Many thanks Anuj For the clarification.It was clear now for me.Anuj Dhawan wrote: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.manikandesvaran wrote:If JOIN is speciified the REFORMAT filed is optional only.But still getting the error.
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page