-
Hi, there
I have a question regarding PL/SQL programming. Reference the following table:
Part No. Case No.
p1 | c1
p2 | c1
p3 | c2
p3 | c2
I need to find whether there is duplication of part No. under the same case No. In the above case, p3 c2 are duplicated. Now I have a program fetching each record from a cursor in a loop. In the loop I want to write this checking. Is there optimized way to write this?
-
Anyone has any idea? Please help. Thanks!
-
Hi shenru,
Here are 2 options.
Test them first, I just wrote it without:
declare
cursor c_check
is
select part_no, case_no, count(0) part_cnt
from the_table
group by part_no, case_no
having count(0) > 1
;
begin
for vc_check in c_check
loop
delete from the_table
where part_no = vc_check.part_no
and case_no = vc_check.case_no
and rownum < vc_check.part_cnt
;
end loop;
end;
/
Or without a cursor:
delete from the_table t1
where (part_no, case_no) in
( select part_no, case_no
from the_table t2
where t1.part_no = t2.part_no
and t1.case_no = t2.case_no
and t1.rowid > t2.rowid
)
;
Regards
Ben de Boer
-
There are more ways ...
1. You can put the actual values in a variable and test them for the equality in the next iteration
2. You can write another query that tells you number of occurences for every Part No + Case No and use it for the cursor
3. As 2. and to join it with the original query
-
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
bensr and ales, thank you very much!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|