How to Split the record in two part and remove the duplicate



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

How to Split the record in two part and remove the duplicate

Postby mainframegeek » Sun Sep 16, 2012 2:02 pm

How to Split the record in two part and remove the duplicate using syncsort?
File 1
01 FILE-REC
05 AREA-NMBR PIC 9(5)
05 BEG-RANGE PIC 9(10)
05 END-RANGE PIC 9(10)

DATA IN FILE 1

0001952000000000100000008
0001952000000001000000020
0001952000000003000000040
0012121000000011100000121
0012121000000015100000191

OUTPUT FILE
0001952000000000100000040
0012121000000011100000191

REQUIREMENT
For a particular area number we have more than one record, out of these records we have to arrange minimum value for BEG-RANGE field and maximum value for END-RANGE in a record.

as per example values of BEG-RANGE for 0001952 AREA-NMBR
0000000001
0000000010
0000000030

out of these we need to get minimum value 0000000001

as per example values of END-RANGE for 0001952 AREA-NMBR
00000008
00000020
00000040
out of these we need to get maximum value 0000000040

and at last we need to put these two values with AREA-NMBR
like 000195200000000010000000040

Likewise we have to do for other records. Area-NMBR is getting repeated but other two fields are unique.
Can anyone help me doing this with the help of synsort 1.3.2 version.
mainframegeek
 
Posts: 35
Joined: Sun Sep 16, 2012 1:57 pm
Has thanked: 3 times
Been thanked: 0 time

Re: How to Split the record in two part and remove the dupli

Postby BillyBoyo » Sun Sep 16, 2012 3:37 pm

Puneet,

Do you have to take the beginning range from the first record for that key and the ending range from the last record for the key?

Or, are you OK with the MINimum beginning value for the key and the MAXimum ending value for the key.

As you already know, the first is difficult and the second is easy.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Split the record in two part and remove the dupli

Postby mainframegeek » Sun Sep 16, 2012 4:25 pm

Values could be arranged in any order.
For Example, this is given above
0001952000000000100000008
0001952000000001000000020
0001952000000003000000040

It could be in any order.Like given below
0001952000000003000000040
0001952000000001000000020
0001952000000000100000008
Output should be
0001952000000001000000040

The purpose is to arrange the records in such a way that minimum value of BEG-RANGE and maximum range of END-RANGE will come for that particular area number in a single record. After doing that single record has to be there, which should have minimum value BEG-RANGE and maximum value of END-RANGE.
mainframegeek
 
Posts: 35
Joined: Sun Sep 16, 2012 1:57 pm
Has thanked: 3 times
Been thanked: 0 time

Re: How to Split the record in two part and remove the dupli

Postby BillyBoyo » Sun Sep 16, 2012 9:49 pm

And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Split the record in two part and remove the dupli

Postby mainframegeek » Sun Sep 16, 2012 10:38 pm

I tried with the solution given below and got the error:
JOINKEYS FILES=F1,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOINKEYS FILES=F2,FIELDS=(26,8,A),SORTED,NOSEQCK
*
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,25,F2:1,5)
INREC IFOUTLEN=26,
IFTHEN=(WHEN=GROUP,BEGIN=(1,5),PUSH=(32:6,10))
*
IFTHEN=(WHEN=(1,5,CH,NE,26,5,CH,OR,31,1,ZD,EQ,1),
*
OVERLAY=(6:32,10,26:C'X'))
*
OUTFIL INCLUDE=(26,1,CH,EQ,C'X'),BUILD=(1,25)
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER251A INCLUDE/OMIT INVALID FORMAT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
mainframegeek
 
Posts: 35
Joined: Sun Sep 16, 2012 1:57 pm
Has thanked: 3 times
Been thanked: 0 time

Re: How to Split the record in two part and remove the dupli

Postby BillyBoyo » Sun Sep 16, 2012 11:43 pm

BillyBoyo wrote:And did you get to try the OUTFIL with SECTIONS and TRAILER3 solution?


You've been told that the JOINKEYS won't work. Let alone anything else, the JNFnCNTL files are not supported.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Split the record in two part and remove the dupli

Postby bodatrinadh » Mon Sep 17, 2012 1:35 pm

Hi,

You can try this snippet..

//STEP2    EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SYSPRINT DD SYSOUT=*       
//SORTIN   DD *               
0001952000000000100000008     
0001952000000001000000020     
0001952000000003000000040     
0012121000000011100000121     
0012121000000015100000191     
//SORTOUT DD SYSOUT=*         
//SYSIN    DD *               
  SORT FIELDS=(1,7,CH,A)     
  DUPKEYS MAX=(18,08,ZD)     


Output :-

0001952000000000100000040   
0012121000000011100000191   
Thanks
-3nadh
User avatar
bodatrinadh
 
Posts: 67
Joined: Thu Jan 12, 2012 9:05 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How to Split the record in two part and remove the dupli

Postby BillyBoyo » Mon Sep 17, 2012 2:13 pm

-3nadh,

That's nice.

Puneet messed up the test data, so 1,5 and 6,10, 16,10 are the fields.

Does it work with MERGE as the file is already in key order?

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)


Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Split the record in two part and remove the dupli

Postby bodatrinadh » Mon Sep 17, 2012 3:27 pm

Hi Billy,

If the test data has 1,5 and 6,10, 16,10 key fields then i need to try some alternate code...

Does it work with MERGE as the file is already in key order?


I tried your code Billy..

OPTION EQUALS
MERGE FIELDS=(1,5,CH,A)
DUPKEYS MAX=(16,10,ZD)


Output i got is
0001952000000003000000040
0012121000000015100000191


Also, any other options on DUPKEYS? Presumably MIN. What about FIRST and LAST?

Is this for 1.4 only? Puneet has 1.3.2.

DUPKEYS will work from 1.3.2 version.
Thanks
-3nadh

These users thanked the author bodatrinadh for the post:
BillyBoyo (Mon Sep 17, 2012 4:01 pm)
User avatar
bodatrinadh
 
Posts: 67
Joined: Thu Jan 12, 2012 9:05 pm
Has thanked: 0 time
Been thanked: 4 times

Re: How to Split the record in two part and remove the dupli

Postby BillyBoyo » Mon Sep 17, 2012 4:01 pm

Excellent -3nadh,

Interesting with the MERGE - keeps the entire MAX record maybe?

I was wondering because with DFSort you can use MERGE with SORTIN01 only and then SUM FIELDS=NONE works with an already sorted file (can't do the SUM FIELDS=NONE with SORT FIELDS=NONE or OPTION,COPY).

Can you just add about other options for the DUPKEYS, please?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post