Archives of the TeradataForum

Message Posted: Wed, 28 Jul 2010 @ 22:55:10 GMT

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

I am not a strong SQL programmer. I read through the manual about Qualify, Row_Count, Partition, etc., but am struggling with trying to get this piece of code to run. It looks like it should. I would appreciate suggestions to get this working.

I am using Teradata SQL Assistant Version 13.0.0.11, April 30th, 2010. I am running the code below to try and bring back the i_ird that has a RT in it, but it s bringing me back two records (one with an EE in it).

     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_ird
     Order by Case When i_ird like 'RT%' then 0 else 1 end) = 1
     Where  i_ssn = 'xxxxxxxxxx'

Result:

     I_POL   I_IRD      N_LST   N_FST   I_SSN   D_BTH
     xxxx    xxxEExxx   xxx     xxx     xxx     x/x/xxxx
     xxxx    xxxRTxxx   xxx     xxx     xxx     x/x/xxxx

What I want:

     I_POL   I_IRD      N_LST   N_FST   I_SSN   D_BTH
     xxxx    xxxRTxxx   xxx     xxx     xxx     x/x/xxxx

Thank you,

Karen

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