I have problems with the query below - it does return the right result set but it's taking quite a bit time (assume good indexes) and I'm looking into simplifying it. Any help I could get, would be greatly appreciated.
Thanks a lot, tom
<PRE>
SELECT distinct
n.cust_id, es.er_id, n.name_id, n.first_name, n.mid_name, n.last_name, n.name_gen, es.cust_id as conflict_cust_id
from name n, er_cust_state es
where n.dsrc_acct_id = es.dsrc_acct_id and
n.name_id = (select max(n2.name_id) from name n2
where n2.dsrc_acct_id in
( select es2.dsrc_acct_id from er_cust_state es2
where es2.cust_id = es.cust_id and es2.er_id = es.er_id )
and
(n2.sys_delete_dt is null
or
n2.sys_delete_dt >= (select distinct min(sc.conflict_dt)
from sep_conflict sc
where sc.conf_cust1 = es.cust_id and sep_test_id = es.er_id)
or
n2.sys_delete_dt >= (select distinct min(sc.conflict_dt)
from sep_conflict sc
where sc.conf_cust2 = es.cust_id and sep_test_id = es.er_id)))
</PRE>