Anyone good at re-writing sql ??

Quote:

---

Originally posted by DaPi
OH YES slimdave ! ! ! !

I obviously need to go on an Analytic Functions Summer Camp - I don't have the right reflexes yet :)

---

you know, I've only used them once in my real working life, as part of an ETL procedure. 99% of the time i use them in this forum.

Quote:

---

Originally posted by slimdave
you know, I've only used them once in my real working life, as part of an ETL procedure. 99% of the time i use them in this forum.

---

You mean to say that this isn't real life? :rolleyes:

Quote:

---

Originally posted by DaPi
You mean to say that this isn't real life? :rolleyes:

---

Not real work-ing life. Unless my dbasupport salary checks have been going to the wrong address.

I see that Oracle have "Senior Support engineer" jobs located just a few miles up the road from me. Sounds like the perfect vacation.

Try the below:

select *
from affected_cat_cust a
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
not exists (SELECT 'x' from affected_catalogue_customer
WHERE hurn = a.hurn
and purn = a.purn
and cat_id = a.cat_id);


also add 2 indexes
1.) affected_cat_cust (hurn,purn,cat_id)
2.) affected_catalogue_customer (hurn,purn,cat_id)

MISSED "and" before the "not exists" in the last post

try this one --

select *
from affected_cat_cust a
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
and not exists (SELECT 'x' from affected_catalogue_customer
WHERE hurn = a.hurn
and purn = a.purn
and cat_id = a.cat_id);


also add 2 indexes
1.) affected_cat_cust (hurn,purn,cat_id)
2.) affected_catalogue_customer (hurn,purn,cat_id)

Thanks Guys, changing the NOT IN to NOT EXISTS was enough to get the statement through in the required timescales. Thanks for all your other suggestions.

So cheers for that DaPi