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!
Here are 2 options.
Test them first, I just wrote it without:
select part_no, case_no, count(0) part_cnt
group by part_no, case_no
having count(0) > 1
for vc_check in c_check
delete from the_table
where part_no = vc_check.part_no
and case_no = vc_check.case_no
and rownum < vc_check.part_cnt
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
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
bensr and ales, thank you very much!
Click Here to Expand Forum to Full Width