-
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
)
;
-
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!