Home » SQL & PL/SQL » SQL & PL/SQL » Getting the error 'ORA-01732' while removing Duplicates (Oracle 10g)
Getting the error 'ORA-01732' while removing Duplicates [message #416717] |
Tue, 04 August 2009 01:38  |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I am trying to remove some duplicates out of a table. The create script is given below.
CREATE TABLE S12_CHECK
(
MECODE VARCHAR2(8) NOT NULL,
APPLNO VARCHAR2(15) NOT NULL,
TESTCODE VARCHAR2(3) NOT NULL,
SOURCE VARCHAR2(10),
DATEOFEXAM DATE NOT NULL,
LIFENO VARCHAR2(2) NOT NULL,
PAYFLG NUMBER(2) NOT NULL,
REASONCANCEL VARCHAR2(200),
REASONCODE VARCHAR2(2)
)
Insert statements :--
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000058', 'S12', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000058', 'FBS', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000058', 'LIP', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000059', 'S12', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000059', 'S12', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000059', 'HV1', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'00000201', '92101000059', 'HV1', 'MISC', TO_Date( '07/01/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 7, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'80000001', '12101000044', 'RUA', 'MISC', TO_Date( '07/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 2, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'80000001', '12101000044', 'S12', 'MISC', TO_Date( '07/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 2, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'80000001', '12101000044', 'LIP', 'MISC', TO_Date( '07/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 2, NULL, NULL);
INSERT INTO S12_CHECK ( MECODE, APPLNO, TESTCODE, SOURCE, DATEOFEXAM, LIFENO, PAYFLG, REASONCANCEL,
REASONCODE ) VALUES (
'80000001', '12101000044', 'ECG', 'MISC', TO_Date( '07/20/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '00', 2, NULL, NULL);
The below code returns the duplicate records.
select * from (SELECT mecode, applno, lifeno, testcode, m.ROWID rid,
ROW_NUMBER () OVER (PARTITION BY m.mecode, m.applno, m.lifeno, m.testcode
ORDER BY m.mecode, m.applno, m.lifeno, m.testcode asc, m.ROWID)rn
FROM s12_check m)
WHERE rn != 1 ;
Now I need to delete the above records, so I am using the below query :-
delete from (SELECT mecode, applno, lifeno, testcode, m.ROWID rid,
ROW_NUMBER () OVER (PARTITION BY m.mecode, m.applno, m.lifeno, m.testcode
ORDER BY m.mecode, m.applno, m.lifeno, m.testcode asc, m.ROWID)rn
FROM s12_check m)
WHERE rn != 1;
But this gives the error :-
ORA-01732: data manipulation operation not legal on this view
Please tell me a way as how to remove the duplicates in this case.
Regards,
Mahi
|
|
|
|
Re: Getting the error 'ORA-01732' while removing Duplicates [message #416731 is a reply to message #416717] |
Tue, 04 August 2009 02:17   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
I got the deletion done by changing the query as below :-
delete from s12_check
where rowid in (select rid from (SELECT applno, lifeno, testcode, m.ROWID rid,
ROW_NUMBER () OVER (PARTITION BY m.applno, m.lifeno, m.testcode
ORDER BY m.applno, m.lifeno, m.testcode asc, m.ROWID)rn
FROM s12_check m)
where rn != 1)
I have to find some records out of this table matching 'S12' criteria and update that record.
The logic goes as :-
For a particular MECODE, Applno.... I have to look into the testcodes.... If the set of testcodes contain 'S12' along with testcodes 'FBS' or 'LIP' then the testcode 'S12' is done for free and hence I have to update the column reasoncancel as 'S12'.
The inserted records are :-
SQL> select mecode, applno, testcode test, dateofexam doe, reasoncancel, reasoncode from s12_check;
MECODE APPLNO TEST DOE REASONCANCEL REASONCODE
-------- --------------- --- --------- -------------- ---------
00000201 92101000058 S12 01-JUL-09
00000201 92101000058 FBS 01-JUL-09
00000201 92101000058 LIP 01-JUL-09
00000201 92101000059 S12 01-JUL-09
00000201 92101000059 HV1 01-JUL-09
80000001 12101000044 RUA 20-JUL-09
80000001 12101000044 S12 20-JUL-09
80000001 12101000044 LIP 20-JUL-09
80000001 12101000044 ECG 20-JUL-09
11 rows selected.
We see that the Applno '92101000058' has testcodes 'S12' done along with FBS and LIP. So S12 would be done free as its done on the same day. similarly for applno '12101000044' the testcode is 'S12' along with 'LIP'...so here also 'S12' would be done free...hence would be updated with 'S12' in column REASONCANCEL.
In case of applno '92101000059' there is no 'LIP' or 'FBS' done on same day for same applno, so no updations.
I hope I am making the logic clear.
So I have written a query to update those records for which I have taken the same table in the query qwice.... and its performance is very low.
UPDATE s12_check
SET payflg = 4,
reasoncancel = 'S12',
reasoncode = '02'
WHERE ROWID IN (
SELECT rid
FROM (SELECT b.ROWID rid, b.applno, b.mecode,
TRUNC (b.dateofexam), b.testcode, b.lifeno
FROM s12_check a, s12_check b
WHERE a.mecode = b.mecode
AND a.applno = b.applno
AND TRUNC (a.dateofexam) = TRUNC (b.dateofexam)
AND a.lifeno = b.lifeno
AND a.testcode = 'S12'
AND a.payflg IN (1,2,7 )
AND b.testcode IN ('FBS', 'LIP')
));
Please suggest a way to write it in different way as I have around 80 lakh records and its getting hanged.
Regards,
Mahi
|
|
|
Re: Getting the error 'ORA-01732' while removing Duplicates [message #416758 is a reply to message #416731] |
Tue, 04 August 2009 04:34  |
tapaskmanna
Messages: 98 Registered: January 2007 Location: Cyprus,Nicosia
|
Member |
|
|
Pls try with BULK UPDATE:
Try with this code
SET SERVEROUTPUT ON
DECLARE
TYPE t_row_id_tab IS TABLE OF s12_check.ROWID%TYPE;
l_row_id_tab t_row_id_tab := t_row_id_tab();
l_start NUMBER;
BEGIN
-- Populate collections.
FOR i IN ( SELECT rid FROM (SELECT b.ROWID rid, b.applno, b.mecode,TRUNC (b.dateofexam), b.testcode, b.lifeno
FROM s12_check A, s12_check b
WHERE A.mecode = b.mecode
AND A.applno = b.applno
AND TRUNC (A.dateofexam) = TRUNC (b.dateofexam)
AND A.lifeno = b.lifeno
AND A.testcode = 'S12'
AND A.payflg IN (1,2,7 )
AND b.testcode IN ('FBS', 'LIP')
)
)
LOOP
l_row_id_tab.extend;
l_row_id_tab(l_row_id_tab.last) := i.rid;
END LOOP;
--Bulk updates.
FORALL i IN l_row_id_tab.first .. l_id_tab.last
UPDATE s12_check
SET payflg = 4,
reasoncancel = 'S12',
reasoncode = '02'
WHERE ROWID=l_row_id_tab(i);
COMMIT;
END;
|
|
|
Goto Forum:
Current Time: Sun Feb 09 21:51:08 CST 2025
|