Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic duplicate search
Dynamic duplicate search [message #253076] |
Sun, 22 July 2007 03:14  |
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   |
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  |
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
|
|
|
Goto Forum:
Current Time: Tue Feb 18 19:34:20 CST 2025
|