Page 2 of 2

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 4:49 am
by BillyBoyo
Yes, enrico has it. Look at your sysout. You will see no SORT has been done. A COPY has been done, and with COPY input and output datasets are open at the same time. Even when SORTing, please don't use the same DSN for input and output in the same ICETOOL operator or the same SORT step.

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 1:55 pm
by Viswanathchandru
Hi All,

I'm not sure, I tried to remove the duplicates for a similar type of records given below with similar sort card using SELECT. I'm getting a single record. I'm not sure whether I'm doing it in the correct way.

Input Data:

VENUS- MAIL MAILABC    TRIGGERED AT TERMINAL BSDFADAA AS RECIPIENT 81928
VENUS- MAIL MAILABX    TRIGGERED AT TERMINAL DWSDFSDF AS RECIPIENT 99182
VENUS- MAIL MAILABC    TRIGGERED AT TERMINAL DWTJSDFE AS RECIPIENT 27273
VENUS- MAIL MAILQAZ    TRIGGERED AT TERMINAL DWSDFDS9 AS RECIPIENT 11234
VENUS- MAIL MAILLOK    TRIGGERED AT TERMINAL DWTSDFS7 AS RECIPIENT 99283
VENUS- MAIL MAILLOK    TRIGGERED AT TERMINAL DSDFSDFC AS RECIPIENT 23887
VENUS- MAIL MAILQWE    TRIGGERED AT TERMINAL AOHOHSKA AS RECIPIENT 99283
VENUS- MAIL MAILSDF    TRIGGERED AT TERMINAL NSJIUNWN AS RECIPIENT 92734
VENUS- MAIL MAILQWE    TRIGGERED AT TERMINAL ABKKJUUW AS RECIPIENT 11822
VENUS- MAIL MAILSDF    TRIGGERED AT TERMINAL SKJKSDFH AS RECIPIENT 19393
VENUS- MAIL MAILQWE    TRIGGERED AT TERMINAL SAKJDFLS AS RECIPIENT 19393
VENUS- MAIL MAILKLJ    TRIGGERED AT TERMINAL SKKJKASF AS RECIPIENT 19938
VENUS- MAIL MILAABC    TRIGGERED AT TERMINAL QUYWOUYW AS RECIPIENT 10347
VENUS- MAIL MAILNMB    TRIGGERED AT TERMINAL BAKJSUUW AS RECIPIENT 18737
VENUS- MAIL MAILQWS    TRIGGERED AT TERMINAL SKSKIFHH AS RECIPIENT 93847
VENUS- MAIL MAILQWS    TRIGGERED AT TERMINAL CHSIIWUU AS RECIPIENT 19277
VENUS- MAIL MAILQWE    TRIGGERED AT TERMINAL ZIASIJIW AS RECIPIENT 10983


Sort JCL:

//OUT DD DSN=VENUS3V.INLOG.TEXT2,DISP=SHR   
//IN DD DSN=VENUS3V.EXCEL.INLOG,DISP=SHR     
//TOOLIN DD *                               
 SELECT FROM(IN) TO(OUT) ON(13,8,CH) LASTDUP


Output :

********************************* Top of Data **********************************
VENUS- MAIL MAILQWE    TRIGGERED AT TERMINAL ZIASIJIW AS RECIPIENT 10983       
******************************** Bottom of Data ********************************


Can someone help me where I'm going wrong !

Note: I have used seperate Infile and outfile for the Sort JCL as suggested.


Thanks,
Viswa..

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 2:07 pm
by enrico-sorichetti
since Your input file is a VB file, using
SELECT FROM(IN) TO(OUT) ON(13,8,CH) LASTDUP


will de-duplicate on ...
    VENUS- MAIL MAILABC    TRIGGERED AT TERMINAL BSDFADAA AS RECIPIENT 81928
1234567890123456789012345678901234567890123456789012345678901234567890
            12345678


and it will get the last record of the set

for a VB dataset with the posted content wiser to use
SELECT FROM(IN) TO(OUT) ON(17,8,CH) LASTDUP

to take into account the 4 bytes of the RDW

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 2:14 pm
by BillyBoyo
I think you want LAST not LASTDUP. Consult the manual to see the difference. LASTDUP is only going to give you the last record in a sequence of duplicate keys. A unique key does not count as duplicate (no real surprise) so will not be selected. With LAST, it gives the last record of a key, whether unique or duplicate.

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 2:27 pm
by Aki88
Hello,

Urmmmm, Vishwa, you want to remove duplicates and sort the i/p file, and that is it, right; and maybe have the sorted record in one file with duplicates in another (because of the usage of LASTDUP)??? :?

If the requirement is to simply sort the file, remove the duplicates, and have them written somewhere, then maybe this SELECT can help you:

SELECT FROM(IN) TO(OUT) ON(17,8,CH) ALLDUPS DISCARD(DUPS)   <-- this is considering the i/p to be VB


Your JCL will have another DD statement, with a DS for 'DUPS'.

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 2:29 pm
by enrico-sorichetti
NOPE ... the TS does not care about the keeping the discarded duplicates

for any key he wants to keep the last occurrence ...
and after that restore the initial sequence ( relative position )

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Wed May 14, 2014 2:34 pm
by BillyBoyo
You need to post the sysout from the step which is not working.

Re: Eleminating Duplicates using SELECT and LASTDUP.

PostPosted: Thu May 15, 2014 12:04 am
by Viswanathchandru
Hi All,

Big Thanks, for addressing my post and I got the required output. Also, I learnt more about SELECT in DFSORT.

Enrico,

Thanks for pointing out that .. I forgot to add 4 bytes as I'm dealing with VB records.

Billy Boyo:

Thanks for the insights on LAST and LASDUP.. I had a different understanding about that..

Thanks All for helping me out !


Thanks,
Viswa..