Archives of the TeradataForum
Message Posted: Fri, 30 Jul 2010 @ 05:18:00 GMT
| 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'.