Help on rank() function [message #259780] |
Thu, 16 August 2007 08:40  |
stalin7k
Messages: 1 Registered: August 2007
|
Junior Member |
|
|
Hi All,
Can someone help me in tuning the below sql using rank() function?
DELETE FROM stc_calllog_ext a
WHERE a.stc_save_status = 'CT'
AND ROWID IN (SELECT a.ROWID
FROM stc_calllog_ext a, calllog_ext b
WHERE a.prod_line_code= b.prod_line_code
AND a.brand_code = b.brand_code
AND a.model_number = b.model_number
AND a.stc_save_status != b.stc_save_status
AND trunc(a.stc_start_time) = trunc(b.stc_start_time)
AND a.stc_start_time BETWEEN TRUNC(SYSDATE-8) AND TRUNC(SYSDATE)
AND a.call_taker_userid = b.call_taker_userid
AND a.cons_telephone_no =b.cons_telephone_no
)
Thanks in advance
|
|
|
|
Re: Help on rank() function [message #259792 is a reply to message #259780] |
Thu, 16 August 2007 09:14  |
Cthulhu
Messages: 381 Registered: September 2006 Location: UK
|
Senior Member |
|
|
Have you tried doing it as a correlated subquery rather than accessing stc_calllog_ext twice ? I've not tested it, but you could probably rewrite it as something like:
DELETE FROM stc_calllog_ext a
WHERE EXISTS
(SELECT 1
FROM calllog_ext b
WHERE a.prod_line_code= b.prod_line_code
AND a.brand_code = b.brand_code
AND a.model_number = b.model_number
AND a.stc_save_status != b.stc_save_status
AND trunc(a.stc_start_time) = trunc(b.stc_start_time)
AND a.call_taker_userid = b.call_taker_userid
AND a.cons_telephone_no =b.cons_telephone_no
)
AND a.stc_start_time BETWEEN TRUNC(SYSDATE-8) AND TRUNC(SYSDATE)
|
|
|