IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare within file & eleminate the duplicate


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Wed Sep 02, 2009 4:49 pm
Reply with quote

Given a single file I want to eliminate only those duplicate records on key which have specific values on a particular field.
e.g. I have 10 fields with A/c no & indicator in simple file which I need to compare account number & indicator. If account has indicator N I need to eliminate that record. Account which has Y & N indicator then those need to write to the output (the whole record in the output )

Input :
---------
A/C Indicator
123 N abcdefgggh1
123 Y abcdefwwgh1
124 N abcdefsdsdh
124 N abcde232ds
125 N abcde656ds
125 Y abcdefggsdh


Expected Output:
---------------------
123 N abcdefgggh1
123 Y abcdefwwgh1
123 N abcdefgggh1
123 Y abcdefwwgh1

Can we compare two fields within a file & write the matching records to the output using DFSORT/SORT icon_rolleyes.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Sep 02, 2009 8:57 pm
Reply with quote

Your description and example are quite confusing.

Are there always two records per A/C, so the only combinations are YN, NY, NN and YY? Or can there be more than two records pre A/C with more combinations?

In your output you have the 123 account records twice - why? What about the 125 account records?

What are the complete set of rules? So far it looks like:

1) YN - keep
2) NY - keep
3) NN - remove
4) YY - ?

Please explain the rules clearly and show a good example of input and output with all possible cases.

Also, what is the RECFM and LRECL of the input file?
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Thu Sep 03, 2009 12:09 pm
Reply with quote

Sorry this is typo mistake!!

My req is I need to remove the records which has 2 N's & 2 Y's, write the only records which has 1 Y & 1 N, the file is sorted on indicator & account number. The input file will only have 2 duplicate records with value N & Y for each account.
The rules are -

1) YN - keep
2) NY - keep
3) NN - remove
4) YY - remove


e.g

Input :
---------
A/C INDC NAME
123 N MR. ALAN
123 Y MR. ALAN
124 N MRS.HELENA
124 N MRS.HELENA
125 N MRS.HELEN
125 Y MRS.HELEN
126 Y MR. ROBERT
126 Y MR. ROBERT

Expected Output:
---------------------
A/C INDC NAME
123 N MR. ALAN
123 Y MR. ALAN
125 N MRS.HELEN
125 Y MRS.HELEN


the RECFM is FB and LRECL - 80 of the input file.

I need to compare within the file & write it to the output. Is this possible.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Thu Sep 03, 2009 9:37 pm
Reply with quote

Assuming that your records are already sorted by positions 1-3, you can use a DFSORT job like the following to do what you asked for:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=... input file (FB/80)
//SORTOUT DD DSN=... output file (FB/80)
//SYSIN    DD    *
  SORT FIELDS=COPY
  OUTREC IFTHEN=(WHEN=GROUP,RECORDS=2,PUSH=(81:1,80,161:SEQ=1)) 
  OUTFIL INCLUDE=(161,1,ZD,EQ,2,AND,9,1,CH,NE,89,1,CH),
    BUILD=(1,80,/,81,80)
/*


If your records are not already sorted by positions 1-3, replace the SORT FIELDS=COPY statement with this statement:

Code:

  SORT FIELDS=(1,3,CH,A),EQUALS
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Fri Sep 04, 2009 3:56 pm
Reply with quote

Hi Frank,

This is not working. I am not getting any records in output. Can you please explain me the sort card, I might be missing some point here
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Sep 04, 2009 8:48 pm
Reply with quote

Hello,

Suggest you post the jcl, sort control, and messages including the message ids from the run that is not working as you want. . .

Posting "This is not working." just wastes everyone's time. . .
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Sat Sep 05, 2009 6:27 am
Reply with quote

It's working for me. If it's not working for you, then you're doing something different or gave me the wrong information.

Were your records already sorted by positions 1-3 (the key)? If not, did you replace the SORT FIELDS=COPY statement with the SORT FIELDS=(1,3,CH,A),EQUALS statement as I mentioned you should?
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Mon Sep 07, 2009 6:09 pm
Reply with quote

Hi Frank,

I just tried the below sort and its working fine with me for the above.

Code:

//S1 EXEC PGM=ICETOOL                                   
//TOOLMSG DD SYSOUT=*                                   
//DFSMSG DD SYSOUT=*                                   
//IN DD *                                               
123 N MR. ALAN                                         
123 Y MR. ALAN                                         
124 N MRS.HELENA                                       
124 N MRS.HELENA                                       
125 N MRS.HELEN                                         
125 Y MRS.HELEN                                         
126 Y MR. ROBERT                                       
126 Y MR. ROBERT                                       
//OUT DD SYSOUT=*                                       
//TOOLIN DD *                                           
SELECT FROM(IN) TO(OUT) ON(1,3,CH) ON(5,1,CH) NODUPS   
/*     


Please correct me if im wrong.

Thanks
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Sep 08, 2009 2:56 am
Reply with quote

Yes, given that the OP wants just the YN and NY records, SELECT with NODUPS would work.

The technique I showed could be used for any combination (e.g. just NN, or just NN and YN).
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top