Archives of the TeradataForum

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

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

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>>