June 6, 2007 at 9:17 am
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, mj
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)))
June 6, 2007 at 10:15 am
Something like the following may help:
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
JOIN er_cust_state es
ON n.dsrc_acct_id = es.dsrc_acct_id
JOIN (
SELECT es2.cust_id, es2.er_id, MAX(n2.name_id) AS name_id
FROM [name] n2
JOIN er_cust_state es2
ON n2.dsrc_acct_id = es2.dsrc_acct_id
LEFT JOIN (
SELECT sc11.conf_cust1, sc11.sep_test_id, MIN(sc11.conflict_dt) AS conflict_dt
FROM sep_conflict sc11
GROUP BY sc11.conf_cust1, sc11.sep_test_id
) SC1
ON es2.cust_id = SC1.conf_cust1
AND es2.er_id = SC1.sep_test_id
LEFT JOIN (
SELECT sc21.conf_cust2, sc21.sep_test_id, MIN(sc21.conflict_dt) AS conflict_dt
FROM sep_conflict sc21
GROUP BY sc21.conf_cust2, sc21.sep_test_id
) SC2
ON es2.cust_id = SC2.conf_cust2
AND es2.er_id = SC2.sep_test_id
WHERE n2.sys_delete_dt IS NULL
OR n2.sys_delete_dt >= SC1.conflict_dt
OR n2.sys_delete_dt >= SC2.conflict_dt
GROUP BY es2.cust_id, es2.er_id
) D
ON es.cust_id = D.cust_id
AND es.er_id = D.er_id
AND n.name_id = D.name_id
June 6, 2007 at 2:08 pm
Thanks a lot for the help.
It looks though that this query returns double size of the data then the original one.
I'll try to modify it.
Thanks a lot again, mj
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply