Need help with subquery IF/THEN statement, or OLAP function

Database
Enthusiast

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

Any help is HUGELY appreciated!

Tags (4)
5 REPLIES
Junior Contributor

Re: Need help with subquery IF/THEN statement, or OLAP function

You probably need a QUALIFY to pick the "best fit", just a wild guess:

LEFT JOIN
(select ... from filter 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) gird

Dieter
Enthusiast

Re: Need help with subquery IF/THEN statement, or OLAP function

I'm getting a syntax error with this as well as other similar test code..

c_pol does not match defined type name?

Thank you very much!!!
Enthusiast

Re: Need help with subquery IF/THEN statement, or OLAP function

and username.gird.nlast does not exist?
Enthusiast

Re: Need help with subquery IF/THEN statement, or OLAP function

Nevermind... Got it!
Enthusiast

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

This worked - Thank you Dieter!