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 Go to next message
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 #416721 is a reply to message #416717] Tue, 04 August 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Deleting duplicates is a FAQ answered hundred times.
Please search BEFORE posting.

Regards
Michel
Re: Getting the error 'ORA-01732' while removing Duplicates [message #416731 is a reply to message #416717] Tue, 04 August 2009 02:17 Go to previous messageGo to next message
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 Go to previous message
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;
Previous Topic: Values b/w 0 and 1 needs to be decoded
Next Topic: problem in union sql statment
Goto Forum:
  


Current Time: Sat Dec 03 15:57:40 CST 2016

Total time taken to generate the page: 0.08517 seconds