Comapare Two Records in the same File

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Comapare Two Records in the same File

Post by raulravi » Fri Apr 29, 2011 10:47 am

Code: Select all

S.no	Name	dept	salary	dept
1	   Raul	Real	2000	  S&F
2	   Raul	ACM	 1000	  R&D
3	   Raul	ACM	 1000	  K&P
I have to read the first recoed and comapre fields in it with the fields in the second record.
When ever there is a mis match i have have to write it to the output recoed and set a indicator.


For example, When i comapre the first record with second in the above example, field dept in record
one is different from record 2

The output should be like

Code: Select all

S.no	Name	NewTeam OldTeam OldSal	NewSal NewDept Old Dept

1	   Raul    real     ACM
Again start comparing, if salary field in record1 is not = to salary field in record2, again write a
output record.

Code: Select all

S.no	Name	NewTeam OldTeam	NewSal	OldSal NewDept OldDept
1	   Raul	 	 			    2000     1000
Again start comparing, if Dept field in record1 is not = to Dept field in record2, again write a
output record.

Code: Select all

S.no	Name	Newteam Oldteam	NewSal	NewSal	NewDept	Olddept
1	Raul	 	 	 	 			                     S&F   R&D
Thanks,
Raul

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

Post by Frank Yaeger » Sat Apr 30, 2011 3:17 am

It's not really clear to me what you want.

Do you only want to compare record 1 and record 2, or do you also want to compare other records, e.g. record 2 and record 3?

Do you want one output file with all of the indicator records or do you want a separate output file for each type of indicator record (3 indicators)? What do you want to do if a field from record 1 matches the field from record 2 (e.g. the dept fields match) - not write any output record?

Please show a better example of your input records and the expected output including compares on more than just record 1 and 2 if you need that, and matching and non-matching fields. Give the RECFM and LRECL of your input file. Give the starting position, length and format of each relevant field.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Jun 22, 2011 1:29 pm

Dear Frank,

Sorry to confuse you... PLease find the example below

Code: Select all

Merged File:			
				
Custid	Name	email 	Salary	date
111	Sandeep	gmail	60000	14-May-11
111	Sandeep	yahoo	60000	01-May-11
111	Sandeep	gmail	60000	01-Mar-11
111	Sandeep	gmail	60000	01-Feb-11
111	Sandeep	gmail	60000	01-Jan-11
222	Raul      gmail	60000	01-Jan-11
In the above file if you look at the 3rd ,4th and 5th records, they are same execpt date. So i want to delete the 4th & 5th record.

Code: Select all

ExpectedOutput:
Merged				
				
Custid	Name	email 	Salary	date
111	Sandeep	gmail	60000	14-May-11
111	Sandeep	yahoo	60000	01-May-11
111	Sandeep	gmail	60000	01-Mar-11
222	Raul      gmail	60000	01-Jan-11
The RECFM is FB and LRECL is 300 for both input/output files.
Length of all the feilds is 10.

Regards,
Ravi

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

Post by Frank Yaeger » Wed Jun 22, 2011 9:37 pm

In the above file if you look at the 3rd ,4th and 5th records, they are same execpt date. So i want to delete the 4th & 5th record.
The first record is also the same, so do you really want to delete the 3rd, 4th and 5th records? You could do that with these control statements:

Code: Select all

  OPTION EQUALS                         
  SORT FIELDS=(1,40,CH,A)               
  SUM FIELDS=NONE                       
Note that if the input records were actually already in sorted order, you could use MERGE and SUM instead of SORT and SUM.

Or do you really want to do the deletes using the original unsorted order of the input records as your example implies?

Please explain what you really want to do more carefully.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Thu Jun 23, 2011 11:23 am

Yes the input records were actually already in sorted order.

And i dont want to delete the 3rd record. I just want to delete the 4th and 5th records. Because I am trying to capture the changes done to the Customer details.

In the first record it is Gmail , and in the 2nd it is Yahoo, so there is a change. And the third record it is again gmail and it is also a change.So i want those records that got changes. But 4th and 5th are same as 3rd . so I want to delete them.

At a time i should compare only two records.
eg: 1 & 2 (Not same keep it) , 2 & 3(Not same keep it) , 3 & 4(same delete it) , 3 & 5 (same delete it).

PLease let me know if i am not clear.

Regards,
Ravi.

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

Post by Frank Yaeger » Thu Jun 23, 2011 8:02 pm

Yes the input records were actually already in sorted order.
The input records you show ARE NOT in sorted order by the first four fields. Sorted order would be:

Code: Select all

111       Sandeep   gmail     60000     14-May-11        
111       Sandeep   gmail     60000     01-Mar-11        
111       Sandeep   gmail     60000     01-Feb-11        
111       Sandeep   gmail     60000     01-Jan-11        
111       Sandeep   yahoo     60000     01-May-11        
222       Raul      gmail     60000     01-Jan-11        
I'm going to assume that what you really want is to keep the records in their original unsorted order and remove duplicates. Here's a DFSORT job that will give you the output you showed for the input you showed. I don't know if that's actually what you want since I don't know if your example illustrates all of the possibilities. If it doesn't give you what you want for some set of records, show the input and expected output and explain the rules for getting from input to output.

Code: Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/300)
//SORTOUT DD DSN=...  output file (FB/300)
//SYSIN DD *
  OPTION COPY
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(1,300),
    SECTIONS=(1,40,
      HEADER3=(1,300))
/*
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Fri Jul 01, 2011 10:11 am

Code: Select all

Input:
Custid Name 	 email	 Salary    date
111 	Frank	 gmail 	 60000 	20110514
111 	Frank	 yahoo 	 60000 	20110501
111 	Frank	 gmail 	 60000 	20110301
111 	Frank	 gmail 	 60000 	20110201
111 	Frank	 gmail 	 60000 	20110101
222 	Raul 	 gmail 	 60000 	20110101

Output:
Custid Name 	 email	 Salary    date
111 	Frank	 gmail 	 60000 	20110514
111 	Frank	 yahoo 	 60000 	20110501
111 	Frank	 gmail 	 60000 	20110301
222 	Raul 	 gmail 	 60000 	20110101
The data in the above file is unloaded from the Maintenance table of customers.
Every month this table is updated with the current values.
IN addition to that if there are any changes made , are also written to the table.

So Frank have a total of 5 Records. But his 4th & 5th records are same as the 3rd one.
So when ever i compare two records, if all the coloums match execpt DATE, then delete that record.

If any two comsecutive records are same execpt date, then delete that record (duplicate).

Eg:
Record-1 is not equal to Record-2 ---- Write to output.
Record-2 is not equal to Record-3 ---- Write to output.
Record-3 is equal to Record-4 & Record5 (only date is different) ---- Delete Record4 & Record5

If we do the normal sort and remove duplicates, even the 3rd Record wil l be deleted, but i want the
3rd Record of Frank. As it is the initial value (gmail).

Hope its clear now....PLease let me know if it is not clear.

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

Post by Frank Yaeger » Sat Jul 02, 2011 1:16 am

Here's a DFSORT job that will do what you asked for:

Code: Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/300)
//SORTOUT DD DSN=...  output file (FB/300)
//SYSIN DD *
  OPTION COPY
  OUTFIL REMOVECC,NODETAIL,
    SECTIONS=(1,40,
      HEADER3=(1,300))
/*
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Tue Jul 05, 2011 4:21 pm

Dear Frank,

Thanks a lot, i am able to get the desired output.

Frank a column in the output file is blank, and i want to fill it with a unique number, like 1 , 2 3

Code: Select all

Input					
Custid	Name	Unique No	email 	Salary 	date 
111  	 Frank        		gmail	60000	 20110514 
111  	 Frank        		yahoo	60000	 20110501 
111  	 Frank        		gmail	60000	 20110301 
222  	 Raul         		gmail	60000	 20110101

Output					
Custid	Name	Unique No	email 	Salary 	date 
111  	 Frank 	1       	gmail	60000	 20110514 
111  	 Frank 	2       	yahoo	60000	 20110501 
111  	 Frank 	3       	gmail	60000	 20110301 
222  	 Raul  	4          gmail	60000	 20110101
Can you please help me out?
Files are FixedBlock and Lrec= 80. each feild length is 10.

Regards,
Ravi

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

Post by Frank Yaeger » Tue Jul 05, 2011 11:05 pm

I can't tell from your example where you want the sequence number to start, how long you want it to be or what format you want it in. But you can just OVERLAY a sequence number where you want it using DFSORT statements like this:

Code: Select all

  OPTION COPY                      
  INREC OVERLAY=(21:SEQNUM,5,ZD)   

Adjust the starting position, length and format (FS if you want to suppress leading blanks) as needed.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Jul 06, 2011 9:36 am

Terrific, it's working as i need........ Thanks a lot frank.

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Jul 13, 2011 10:45 am

Hi Frank,

i am removed duplicates as below.

Code: Select all

//S1 EXEC PGM=SORT 
//SYSOUT DD SYSOUT=* 
//SORTIN DD DSN=...  input file (FB/300) 
//SORTOUT DD DSN=...  output file (FB/300) 
//SYSIN DD * 
  OPTION COPY 
  OUTFIL REMOVECC,NODETAIL, 
    SECTIONS=(1,40, 
      HEADER3=(1,300)) 
/* 
But now i have two file's. I need to first merge them in to single file and then remove duplicates as above. Can i add the merge step in the above job? how?

Raul

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

Post by Frank Yaeger » Wed Jul 13, 2011 10:27 pm

I have no idea what you're asking for.

You need to start over and give a clear description of what you're trying to do with a good example of input records and expected output records.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Thu Jul 14, 2011 11:33 am

I Have a three files.

Code: Select all

File1: 
custid	Name	email	club
1        Ravi   gmail	 real
1   	  Ravi   yahoo	 real
2     	kiran  gmail	fcb
2     	kiran  gmail	fcb
3     	arun	yahoo 	manu
4     	raja	rediff	fcb

Code: Select all

File2:
Email Id	Code
Gmail   	1111G
Yahoo   	2222Y
Rediff  	3333R
inbox   	4444I

Code: Select all

File3:
club	Code
real	aaa1
fcb 	bbb2
manu	ccc3

I want to replace the Email & Club column's with respective codes.

Code: Select all

Output file:
 output file			
			
custid	Name	email	club
1     	Ravi	1111G	aaa1
1     	Ravi	2222Y	aaa1
2     	kiran  1111G   bbb2
2     	kiran  1111G   bbb2
3     	arun	2222Y	ccc3
4     	raja	3333R	bbb2
How can i do that this replacement?

Raul

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 Jul 15, 2011 12:53 am

Here's a DFSORT/ICETOOL job for your new requirement. I assumed your input files all had RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code: Select all

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
1       ravi    gmail   real
1       ravi    yahoo   real
2       kiran   gmail   fcb
2       kiran   gmail   fcb
3       arun    yahoo   manu
4       raja    rediff  fcb
//IN2 DD *
gmail           1111G
yahoo           2222Y
rediff          3333R
inbox           4444I
//IN3 DD *
real    aaa1
fcb     bbb2
manu    ccc3
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY JKFROM TO(T1) USING(CTL1)
SORT JKFROM TO(OUT) USING(CTL2)
//CTL1CNTL DD *
  JOINKEYS F1=IN1,FIELDS=(17,6,A)
  JOINKEYS F2=IN2,FIELDS=(1,6,A)
  REFORMAT FIELDS=(F1:1,16,F2:17,6,F1:23,58)
  OPTION COPY
//CTL2CNTL DD *
  JOINKEYS F1=T1,FIELDS=(25,4,A)
  JOINKEYS F2=IN3,FIELDS=(1,4,A)
  REFORMAT FIELDS=(F1:1,24,F2:9,4,F1:29,52)
  SORT FIELDS=(1,1,ZD,A),EQUALS
/*
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

Locked

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