Hi Tom,

Can you please suggest the best way to use case statements while comparing fields between two tables.

create table t1(id number, first_name varchar2(20), last_name varchar2(20), birth_date date);

insert into t1 (id, first_name, last_name, birth_date) values (1, 'John', 'Walker', '19500101');
insert into t1 (id, first_name, last_name, birth_date) values (2, 'Haley', 'Grant-Boon', '19510101');
insert into t1 (id, first_name, last_name, birth_date) values (3, 'Nikki', 'Alaska Santa', '19530101');
insert into t1 (id, first_name, last_name, birth_date) values (4, 'Jimmy', 'Cole Haan', '19540101');
insert into t1 (id, first_name, last_name, birth_date) values (5, 'Neil', 'O''Brian', '19550101'); ----- The last_name is O'Brian. I used two quotes to enable successful insert.

create table t2(id number, first_name varchar2(20), last_name varchar2(20), birth_date date);

insert into t2 (id, first_name, last_name, birth_date) values (1, 'John', 'Walker', '19500101');
insert into t2 (id, first_name, last_name, birth_date) values (2, 'Haley', 'Grant Boon', '19510101');
insert into t2 (id, first_name, last_name, birth_date) values (3, 'Nikki', 'Alaska Santa', '19530101');
insert into t2 (id, first_name, last_name, birth_date) values (4, 'Jimmy', 'Cole-Haan', '19540101');
insert into t1 (id, first_name, last_name, birth_date) values (4, 'Neil', 'OBrian', '19550101');


select t1.id, t1.first_name, t1.last_name
,t2.id, t2.first_name, t2.last_name
from t1, t2
where t1.birth_date = t2.birth_date
and t1.id = t2.id
----
---- FirstName Check logic
----
AND (CASE
WHEN REGEXP_INSTR (SUBSTR (t1.first_name, 1, 10),'[([:blank:]-,*_'')+]') > 0 THEN
SUBSTR (SUBSTR (t1.first_name, 1, 10), 1, REGEXP_INSTR (SUBSTR (t1.first_name, 1, 10), '[([:blank:]-,*_'')+]') - 1)
ELSE
SUBSTR (t1.first_name, 1, 10)
END) = (CASE
WHEN REGEXP_INSTR (substr(t2.First_Name,1,10), '[([:blank:]-,*_'')+]') > 0 THEN
SUBSTR (substr(t2.First_Name,1,10),1,REGEXP_INSTR (substr(t2.First_Name,1,10),'[([:blank:]-,*_'')+]')- 1)
ELSE
substr(t2.First_Name,1,10)
END)
-----------------------------------
---- LastName Check logic
-----------------------------------
AND (CASE
WHEN REGEXP_INSTR (SUBSTR (t1.last_name, 1, 14),'[([:blank:]-,*_)+]') > 0 THEN
SUBSTR (SUBSTR (t1.last_name, 1, 14), 1, REGEXP_INSTR (SUBSTR (t1.last_name, 1, 14),'[([:blank:]-,*_)+]')- 1)
ELSE
SUBSTR (t1.last_name, 1, 14)
END) = (CASE
WHEN REGEXP_INSTR (substr(t2.Last_Name,1,14),'[([:blank:]-,*_)+]') > 0 THEN
SUBSTR (substr(t2.Last_Name,1,14), 1,REGEXP_INSTR (substr(t2.Last_Name,1,14),'[([:blank:]-,*_)+]') - 1)
ELSE
substr(t2.Last_Name,1,14)
END)
)

Can you please help me with the above query. Is it efficient to use case statement for comparing the values.

We would like to compare the names i.e OBrian = O'Brian. and Cole-Haan = Cole Haan. In these cases the record should return true.

Thanks

SQL> create table t1(id number, first_name varchar2(20), last_name varchar2(20), birth_date varchar2(20));

Table created.

SQL>
SQL> insert into t1 (id, first_name, last_name, birth_date) values (1, 'John', 'Walker', '19500101');

1 row created.

SQL> insert into t1 (id, first_name, last_name, birth_date) values (2, 'Haley', 'Grant-Boon', '19510101');

1 row created.

SQL> insert into t1 (id, first_name, last_name, birth_date) values (3, 'Nikki', 'Alaska Santa', '19530101');

1 row created.

SQL> insert into t1 (id, first_name, last_name, birth_date) values (4, 'Jimmy', 'Cole Haan', '19540101');

1 row created.

SQL> insert into t1 (id, first_name, last_name, birth_date) values (5, 'Neil', 'O''Brian', '19550101');

1 row created.

SQL>
SQL> create table t2(id number, first_name varchar2(20), last_name varchar2(20), birth_date varchar2(20));

Table created.

SQL> insert into t2 (id, first_name, last_name, birth_date) values (1, 'John', 'Walker', '19500101');

1 row created.

SQL> insert into t2 (id, first_name, last_name, birth_date) values (2, 'Haley', 'Grant Boon', '19510101');

1 row created.

SQL> insert into t2 (id, first_name, last_name, birth_date) values (3, 'Nikki', 'Alaska Santa', '19530101');

1 row created.

SQL> insert into t2 (id, first_name, last_name, birth_date) values (4, 'Jimmy', 'Cole-Haan', '19540101');

1 row created.

SQL> insert into t2 (id, first_name, last_name, birth_date) values (5, 'Neil', 'OBrian', '19550101');

1 row created.

SQL>
SQL> with t1x as
  2    ( select id,
  3             lower(regexp_replace(last_name,'[^[:alpha:]]*')) last_name,
  4             lower(regexp_replace(first_name,'[^[:alpha:]]*')) first_name,
  5             birth_date
  6      from   t1 ),
  7   t2x as
  8    ( select id,
  9             lower(regexp_replace(last_name,'[^[:alpha:]]*')) last_name,
 10             lower(regexp_replace(first_name,'[^[:alpha:]]*')) first_name,
 11             birth_date
 12      from   t2 )
 13  select
 14    t1.id,
 15    case
 16      when t1.last_name = t2.last_name and t1.first_name = t2.first_name then
 17            'BOTH CHAR MATCH'
 18      when  ( t1.last_name = t2.last_name and utl_match.edit_distance(t1.first_name,t2.first_name) <= 2  ) or
 19            ( t1.first_name = t2.first_name and utl_match.edit_distance(t1.last_name,t2.last_name) <= 2  ) then
 20            'ONE NAME MATCH, CLOSE ON OTHER'
 21      when  t1.last_name = t2.last_name or t1.first_name = t2.first_name then
 22            'ONE NAME MATCH'
 23    end matching
 24  from t1x t1, t2x t2
 25  where t1.birth_date = t2.birth_date
 26  and t1.id = t2.id;

        ID MATCHING
---------- ------------------------------
         1 BOTH CHAR MATCH
         2 BOTH CHAR MATCH
         3 BOTH CHAR MATCH
         4 BOTH CHAR MATCH
         5 BOTH CHAR MATCH

5 rows selected.

SQL>