View previous topic :: View next topic
|
Author |
Message |
Help-Me-Out
New User
Joined: 09 Dec 2006 Posts: 56 Location: Pune
|
|
|
|
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 |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
Help-Me-Out
New User
Joined: 09 Dec 2006 Posts: 56 Location: Pune
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
Help-Me-Out
New User
Joined: 09 Dec 2006 Posts: 56 Location: Pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
gayathrinag
New User
Joined: 16 Oct 2008 Posts: 37 Location: chennai
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
|