JOIN UNPAIRED,F1,F2,ONLY

In this Mainframe Forum - You can post your queries on JCL, OS/390 JCL, MVS JCL, z/OS JCL, JES2 & JES3

Moderators: Frank Yaeger, DikDude, Moderator Group

Post Reply
tirupathi rao
Member
Posts: 4
Joined: Thu Feb 16, 2012 5:03 pm

JOIN UNPAIRED,F1,F2,ONLY

Post by tirupathi rao » Fri Mar 23, 2012 6:40 pm

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.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Fri Mar 23, 2012 7:48 pm

REFORMAT FIELDS=(F1:1,40,F2:1,40)

40 + 40 = 80


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

40 = 40
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Mar 23, 2012 11:13 pm

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.
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

tirupathi rao
Member
Posts: 4
Joined: Thu Feb 16, 2012 5:03 pm

Post by tirupathi rao » Sat Mar 24, 2012 12:39 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.....

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Mar 26, 2012 10:57 pm

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.
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

manikandesvaran
Member
Posts: 5
Joined: Wed Apr 03, 2013 10:28 pm

Getting error message while using Jointkeys

Post by manikandesvaran » Wed Apr 03, 2013 10:36 pm

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 ?

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Wed Apr 03, 2013 11:56 pm

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

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Thu Apr 04, 2013 2:58 am

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.

manikandesvaran
Member
Posts: 5
Joined: Wed Apr 03, 2013 10:28 pm

Post by manikandesvaran » Thu Apr 04, 2013 2:04 pm

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.

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Re: Getting error message while using Jointkeys

Post by Anuj Dhawan » Fri Apr 05, 2013 1:23 pm

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.
Regards,
Anuj

manikandesvaran
Member
Posts: 5
Joined: Wed Apr 03, 2013 10:28 pm

Re: Getting error message while using Jointkeys

Post by manikandesvaran » Tue Apr 09, 2013 5:58 pm

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.

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Wed Apr 10, 2013 2:48 pm

You're welcome.
Regards,
Anuj

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Feb 01, 2016 10:24 pm

<< Content deleted By Admin >>

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Feb 01, 2016 10:27 pm

<< Content deleted By Admin >>

Post Reply

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



Other References
Mainframe Tools and others