Archives of the TeradataForum
Message Posted: Thu, 29 Jul 2010 @ 21:13:49 GMT
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!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|