Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Deleting unwanted/duplicate records
From:   Hill, Karen R

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


  <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