Home » Server Options » Text & interMedia » matching names from two tables (10g)
matching names from two tables [message #645025] Mon, 23 November 2015 23:17 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i have to match names of employees from two different tables , and get the nearest matching name . it is like using like operator at both sides.


CREATE TABLE OUSERS_TEMP_EMP
(
  UCOMP_CODE      VARCHAR2(4 BYTE),
  UUSER_ID        VARCHAR2(40 BYTE),
  UUSER_GROUP_ID  VARCHAR2(60 BYTE),
  UUG_DESC        VARCHAR2(2000 BYTE)
)


INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'HANEF', 'RAK_ADHT', 'RAK ADMIN HOTEL AND TICKET BOOKING GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SHABITH', 'RAK_ADHT', 'RAK ADMIN HOTEL AND TICKET BOOKING GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'VIJESH', 'RAK_ADMENT', 'RAK FINANCE ENTRY GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SHARMA', 'RAK_ADMIN', 'RAK ADMINISTRATION DEPARTMENT GROUP'); 
INSERT INTO OUSERS_TEMP_EMP ( UCOMP_CODE, UUSER_ID, UUSER_GROUP_ID,
UUG_DESC ) VALUES ( 
'RAK', 'SADDIK', 'RAK_FINAD', 'FINANCE ADMIN'); 
COMMIT;


UCOMP_CODE,UUSER_ID,UUSER_GROUP_ID,UUG_DESC
RAK,HANEF,RAK_ADHT,RAK ADMIN HOTEL AND TICKET BOOKING GROUP
RAK,SHABITH,RAK_ADHT,RAK ADMIN HOTEL AND TICKET BOOKING GROUP
RAK,VIJESH,RAK_ADMENT,RAK FINANCE ENTRY GROUP
RAK,SHARMA,RAK_ADMIN,RAK ADMINISTRATION DEPARTMENT GROUP
RAK,SADDIK,RAK_FINAD,FINANCE ADMIN

CREATE TABLE PM_EMP_MAST
(
  EMP_CODE  VARCHAR2(12 BYTE),
  EMP_NAME  VARCHAR2(30 BYTE)
);



INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R1054', 'SHABITH CHAKKAPPOYAN'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0303', 'MOHAMMAD HANIF MUHAMMAD'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R2029', 'HANIF'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R2881', 'VIJESH PAI THEKKATE'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0174', 'JITENDRA KUMAR SHARMA'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R1911', 'JITENDRA SHARMA'); 
INSERT INTO PM_EMP_MAST ( EMP_CODE, EMP_NAME ) VALUES ( 
'R0613', 'SADDIK M. PURAYIL'); 
COMMIT;


SELECT EMP_CODE,EMP_NAME FROM PM_EMP_MAST

EMP_CODE,EMP_NAME
R1054,SHABITH CHAKKAPPOYAN
R0303,MOHAMMAD HANIF MUHAMMAD
R2029,HANIF
R2881,VIJESH PAI THEKKATE
R0174,JITENDRA KUMAR SHARMA
R1911,JITENDRA SHARMA
R0613,SADDIK M. PURAYIL



--the output desired will be as follows.

emp_code,emp_name,uuser_id
R1054,SHABITH CHAKKAPPOYAN,SHABITH
R0613,SADDIK M. PURAYIL,SADDIK
R0303,MOHAMMAD HANIF MUHAMMAD,HANEF
R2029,HANIF,HANEF
R2881,VIJESH PAI THEKKATE,VIJESH
R0174,JITENDRA KUMAR SHARMA,SHARMA
R1911,JITENDRA SHARMA,SHARMA










Re: matching names from two tables [message #645050 is a reply to message #645025] Tue, 24 November 2015 15:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following obtains the desired result but may not scale well.

SCOTT@orcl> CREATE INDEX emp_name_idx ON pm_emp_mast (emp_name) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

SCOTT@orcl> COLUMN uuser_id FORMAT A20
SCOTT@orcl> SELECT emp_code, emp_name,
  2  	    MAX (uuser_id) KEEP (DENSE_RANK LAST ORDER BY score) uuser_id
  3  FROM   (SELECT SCORE (1) score, emp_code, emp_name, uuser_id
  4  	     FROM   pm_emp_mast, ousers_temp_emp
  5  	     WHERE  CONTAINS (emp_name, 'FUZZY (' || uuser_id || ', 1, 5000, W)', 1) > 0)
  6  GROUP  BY emp_code, emp_name
  7  /

EMP_CODE     EMP_NAME                       UUSER_ID
------------ ------------------------------ --------------------
R0174        JITENDRA KUMAR SHARMA          SHARMA
R0303        MOHAMMAD HANIF MUHAMMAD        HANEF
R0613        SADDIK M. PURAYIL              SADDIK
R1054        SHABITH CHAKKAPPOYAN           SHABITH
R1911        JITENDRA SHARMA                SHARMA
R2029        HANIF                          HANEF
R2881        VIJESH PAI THEKKATE            VIJESH

7 rows selected.

Re: matching names from two tables [message #645052 is a reply to message #645050] Tue, 24 November 2015 22:05 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks very much.
Previous Topic: Oracle Text tuning
Next Topic: contains query not returning expected results
Goto Forum:
  


Current Time: Tue Mar 19 03:33:15 CDT 2024