Need help with subquery IF/THEN statement, or OLAP function
I have a pretty complex query that pulls the following "Insured IDs" from a table specified in the coalesce statement. The problem is that there are duplicate records with the exception of two letters in a string. I want to be able to only bring in the record with the "RT" In the string. I realize i might have to use a substring of sorts to accomplish this as well as a subquery? Perhaps an IF statement?
This is what the data looks like:
42078 00561070 610000000 06042129 Smith Joe 00561000000000000EE19470123F 42078 00561070 610000000 06042129 Smith Joe 00561000000000000RT19470123F
In the last column there is an "EE" and "RT" in the string, If the record has RT, i want to pull that, and not the record with the EE in the string of the last column.... I hope this makes sense....
The code is as follows
,Coalesce (idv.i_ird ,profl.sorce_cust_id ,kird.i_ird ,gird.i_ird <-------------------------------- This is the table with the "Duplicate" Records ,(rtrim(adpt.c_pol)||cast(adpt.c_typ_sys_idv as decimal format '999')|| cast(adpt.i_sys_idv as decimal format '99999'))) AS InsuredID
FROM pearl_p.tltc900_clm clm
The join for this table is here:
LEFT JOIN ltc_p.vltc_er_ird_mo gird ON clm.c_pol = gird.i_pol AND (LastName = gird.n_lst or FirstName = gird.n_fst or SSN = gird.i_ssn)AND DOB = gird.d_bth
Re: Need help with subquery IF/THEN statement, or OLAP function
Select i_pol ,i_ird ,n_lst ,n_fst ,i_ssn ,d_bth From ltc_p.vltc_er_ird_mo gird Where gird.i_ssn = '610000000' Qualify Row_Number () over (partition by i_pol order by case when i_ird like 'EE%' then 0 else 1 end) = 1