Home » SQL & PL/SQL » SQL & PL/SQL » Help on rank() function
Help on rank() function [message #259780] Thu, 16 August 2007 08:40 Go to next message
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 #259787 is a reply to message #259780] Thu, 16 August 2007 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Why do you want to use rank? Where is there a rank in your query?

Regards
Michel
Re: Help on rank() function [message #259792 is a reply to message #259780] Thu, 16 August 2007 09:14 Go to previous message
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)


Previous Topic: Procedure owner rights works with DML but not with DDL
Next Topic: group_by conditions
Goto Forum:
  


Current Time: Sat Dec 10 09:12:49 CST 2016

Total time taken to generate the page: 0.11859 seconds