Archives of the TeradataForum

Message Posted: Thu, 29 Jul 2010 @ 21:13:49 GMT

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

Thank you for your suggestions. I dropped the "PARTITION BY I_IRD" and quickly learned that I need to partition by something, because when I ran through more than one SSN, it still only brought back one row. I changed the partition to look at another column and I'm getting one row per i_ird, which is great!

However, as you stated, I'm getting back other values besides the 'RT', which I don't want. What code should be used to select the 'RT' out of the i_ird string? I tried substring(18,2) = 'RT', but that didn't work either.

     Select i_pol
     ,i_ird
     ,n_lst
     ,n_fst
     ,i_ssn
     ,d_bth
     From ltc_p.vltc_er_ird_mo
     Qualify Row_Number () Over (Partition by i_pol
     Order by Case When i_ird like 'RT%' then 0 else 1 end) = 1
     Where  i_ssn in ('xxxxxxxxx', 'aaaaaaaaa')

For the other suggestions I received (the 'Where' clause and the 'Rank'), they didn't work. I kept getting dupes. And for the email from Jay: If you use [column_name] LIKE '%RT%' you'll get the rows that contain the string 'RT' anywhere in the column., I'm not sure why it's not working. It seems like it can't see the code?

Thank you all for you help!

Karen

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