Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 30 Jul 2010 @ 05:18:00 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Deleting unwanted/duplicate records
From:   Fred W Pluebell

OK - it's still not clear what uniquely identifies a "member" (or how you tell some rows are "duplicates"). You want to PARTITION BY the unique identifier. Is that I_POL, I_SSN? Or I_POL plus some substring(s) of I_IRD? Or is it something else entirely?

As far as picking the right row within a "partition" that has duplicates: Does the RT/EE code have to be in a specific position within I_IRD? If so, then a SUBSTRING expression would be a better choice than LIKE pattern match. If the position varies but the characters "RT" never occur together within I_IRD for anything other than "retiree" records, then you can use LIKE '%RT%' in your CASE expression. Note that the leading "%" is important if you intend to match "starting anywhere in the string". If you say LIKE 'RT%' then the first two character positions must contain "RT" in order to match, so it's essentially the same as saying SUBSTRING(I_IRD FROM 1 FOR 2) = 'RT'.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019