Eleminating Duplicates using SELECT and LASTDUP.



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby BillyBoyo » Wed May 14, 2014 4:49 am

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Re: Eleminating Duplicates using SELECT and LASTDUP.

 

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby Viswanathchandru » Wed May 14, 2014 1:55 pm

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..
Viswanathchandru
 
Posts: 271
Joined: Mon Oct 25, 2010 2:24 pm
Has thanked: 25 times
Been thanked: 0 time

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby enrico-sorichetti » Wed May 14, 2014 2:07 pm

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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
Viswanathchandru (Thu May 15, 2014 12:02 am)
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby BillyBoyo » Wed May 14, 2014 2:14 pm

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.

These users thanked the author BillyBoyo for the post:
Viswanathchandru (Thu May 15, 2014 12:01 am)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby Aki88 » Wed May 14, 2014 2:27 pm

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'.
Aki88
 
Posts: 371
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby enrico-sorichetti » Wed May 14, 2014 2:29 pm

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 )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
Aki88 (Wed May 14, 2014 2:44 pm)
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby BillyBoyo » Wed May 14, 2014 2:34 pm

You need to post the sysout from the step which is not working.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Eleminating Duplicates using SELECT and LASTDUP.

Postby Viswanathchandru » Thu May 15, 2014 12:04 am

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..
Viswanathchandru
 
Posts: 271
Joined: Mon Oct 25, 2010 2:24 pm
Has thanked: 25 times
Been thanked: 0 time

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post