Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic duplicate search
Dynamic duplicate search [message #253076] Sun, 22 July 2007 03:14 Go to next message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Dear Members,

Table cust_mast contains customer data. Table creation and insert Scripts are as below:

CREATE TABLE cust_mast (custid VARCHAR2(9) primary key , cfname VARCHAR2(50), clname VARCHAR2(50), cdob DATE,
                        fathername VARCHAR2(50), mothername VARCHAR2(50), spousename VARCHAR2(50),
                        cpassportno VARCHAR2(30), caddress1 VARCHAR2(60), caddress2 VARCHAR2(60),
                        cphoneno VARCHAR2(20), cmobileno VARCHAR2(20));		

INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000001', 'Rajesh', 'Chavan',  TO_Date( '06/15/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Reema', 'Sumati', 'Lata', 'A20365432', NULL, 'Mumbai', '42354990', '2036498797'); 

INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000002', 'Sanjay', 'Karmakar',  TO_Date( '02/02/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Birendra', 'Ruhi', 'Lavanya', 'D87215389', NULL, 'Kolkata', '71265381', '7128123588');
 
INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000003', 'Debashish', 'Chattarji',  TO_Date( '12/07/1970 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Thakur', 'Seema', 'Reema', 'F82154812', NULL, 'Kolkata', '78215t48', '8236401297');
 
INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000004', 'Gurumit', 'Singh',  TO_Date( '01/09/1975 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Satnam', 'Surjeet', 'Juhi', 'H7254721', NULL, 'Chandigarh', '8621649', '9327439779');
 
INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000005', 'Rajesh', 'Namboodri',  TO_Date( '04/22/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Suresh', 'Kanchan', 'Laxmi', 'G8723615', NULL, 'Chennai', '8123766', '8921364999');
 
INSERT INTO CUST_MAST ( CUSTID, CFNAME, CLNAME, CDOB, FATHERNAME, MOTHERNAME, SPOUSENAME,
CPASSPORTNO, CADDRESS1, CADDRESS2, CPHONENO, CMOBILENO ) VALUES ( 
'00000006', 'Rajesh', 'Namboodri',  TO_Date( '04/22/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'Suresh', 'Kanchan', 'Laxmi', 'G8723615', NULL, 'Chennai', '8123766', '8921364999');
 
COMMIT;


My procedure is as below:

CREATE OR REPLACE PROCEDURE dupchk (
   idno     IN   VARCHAR2,
   pfname   IN   NUMBER,
   plname        NUMBER,
   pdob          NUMBER
)
IS
   verrmsg   VARCHAR2 (1000);
BEGIN
   DELETE FROM cust_dup;

   IF pfname = 1 AND pdob = 1 AND plname = 1
   THEN
      INSERT INTO cust_dup
         SELECT *
           FROM cust_mast
          WHERE    TRIM (cfname)
                || TRIM (clname)
                || TRIM (TO_CHAR (cdob, 'RRRRMMDD')) =
                   (SELECT    TRIM (cfname)
                           || TRIM (clname)
                           || TRIM (TO_CHAR (cdob, 'RRRRMMDD'))
                      FROM cust_mast
                     WHERE TRIM (custid) = TRIM (idno))
            AND TRIM (custid) <> TRIM (idno);
   END IF;

   IF pfname = 1 AND pdob = 1
   THEN
      INSERT INTO cust_dup
         SELECT *
           FROM cust_mast
          WHERE TRIM (cfname) || TRIM (TO_CHAR (cdob, 'RRRRMMDD')) =
                   (SELECT TRIM (cfname) || TRIM (TO_CHAR (cdob, 'RRRRMMDD'))
                      FROM cust_mast
                     WHERE TRIM (custid) = TRIM (idno))
            AND TRIM (custid) <> TRIM (idno);
   END IF;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      verrmsg := SQLERRM;
END;


exec dupchk('00000006',1,1,1)

select * from cust_dup;

CUSTID	CFNAME	CLNAME	CDOB	FATHERNAME	MOTHERNAME	SPOUSENAME	CPASSPORTNO	CADDRESS1	CADDRESS2	

CPHONENO	CMOBILENO

00000005	Rajesh	Namboodri	22-Apr-1980	Suresh	Kanchan	Laxmi	G8723615		Chennai	8123766	

8921364999
00000001	Rajesh	Chavan	22-Apr-1980	Reema	Sumati	Lata	A20365432		Mumbai	42354990	

2036498797
00000005	Rajesh	Namboodri	22-Apr-1980	Suresh	Kanchan	Laxmi	G8723615		Chennai	8123766	

8921364999



Here I am searching for duplicate records based on the criteria what users select (passed 1 in procedure parameters)
and insert then in cust_dup table.

Here I am using only CFNAME, CLNAME and CDOB values for duplicate search.
User may select any combination and accordingly search is to be done.

Do not know how this will happen dynamically. Any help will be appreciated.

Thanks

Raju
Re: Dynamic duplicate search [message #253353 is a reply to message #253076] Mon, 23 July 2007 10:36 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I am not sure how much it will work. procedure calling time you have to specify the procedure argument name with value. so according to user selection you can pass default value for reset parameter.

some think like that..

dupchk (idno =>'00000006',pfname =>1, plname =>1,pdob =>1);

--Yash
Re: Dynamic duplicate search [message #253368 is a reply to message #253353] Mon, 23 July 2007 10:59 Go to previous message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Hi,

Right now procedure is searching on combination of just fname, lname and dob.
But the requirement is customer may select any field out of CFNAME CLNAME CDOB FATHERNAME MOTHERNAME SPOUSENAME CPASSPORTNO CADDRESS1 CADDRESS2
CPHONENO CMOBILENO

and procedure should search based on runtime values provided by user from front end.

For example if the procedure call is like:

dupchk (idno =>'00000006',pfname =>0, plname =>0,pdob =>0, pfathername=>1, pmothername=>0, pspousename=>0, ppassportno=>1, paddress1=>0, paddress=>0, pphoneno=>0, pmobileno=>0 );

In this case search will be based on combination of (pfathername || ppassportno)

likewise based on value passed as 1, combined search should be made for finding duplicate.

I hope this is clear.

Thanks for your response.

Raju

Previous Topic: datatype
Next Topic: ORA-12801
Goto Forum:
  


Current Time: Sat Dec 03 16:27:15 CST 2016

Total time taken to generate the page: 0.08420 seconds