Matching SQL
861832Jun 13 2011 — edited Jun 14 2011Hello
I've created a matching schema that is working but is not picking up the results Im expecting.
The matching code works by linking two tables together by the root of a company name and its activities, entitys and locations. The problem I am expereincing is that it finds the tight matches perfectly, but can not find the matches where one column is not null and one is null. I can do this in differenet passes of SQL but I really want to be able to do it in one hit instead of building mulitple lines of code.
Below is the sql to build both tables, I will put the Matching SQL in a new post
h1. Table 1
CREATE TABLE SCOTT."COMPANY_TABLE1"
( "ROOT" VARCHAR2(255 BYTE),
"COMPANY" VARCHAR2(255 BYTE),
"ST_COUNTRY" VARCHAR2(255 BYTE),
"ST_COID" VARCHAR2(255 BYTE),
"ACTIVITY" VARCHAR2(255 BYTE),
"ENTITY" VARCHAR2(255 BYTE),
"LOCATION" VARCHAR2(255 BYTE)
) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE SCOTT ;
REM INSERTING into SCOTT.COMPANY_TABLE1
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('meggitt','MEGGITT DEFENCE SYSTEMS LTD','united kingdom','100','//Defence//Systems','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('meggitt','MEGGITT DEFENCE SYSTEMS LTD','united kingdom','101','//Defence//Systems','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ge','GE HEALTHCARE LTD','united kingdom','110','//Healthcare','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ge','GE HEALTHCARE LTD','united kingdom','111','//Healthcare','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ge','GE HEALTHCARE LTD','united kingdom','112','//Healthcare','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ge','GE HEALTHCARE LTD','united kingdom','113','//Healthcare','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pilkington','PILKINGTON PLC','united kingdom','129',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pilkington','PILKINGTON PLC','united kingdom','130',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pilkington','PILKINGTON PLC','united kingdom','132',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pilkington','PILKINGTON PLC','united kingdom','133',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('random','RANDOM HOUSE UK LTD','united kingdom','144','//House','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('random','RANDOM HOUSE UK LTD','united kingdom','145','//House','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rankhovis','RANK HOVIS LTD','united kingdom','151',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS ROYCE POWER ENGINEERING PLC','united kingdom','156','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS ROYCE POWER ENGINEERING PLC','united kingdom','157','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS ROYCE POWER ENGINEERING PLC','united kingdom','158','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS ROYCE POWER ENGINEERING PLC','united kingdom','159','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS ROYCE POWER ENGINEERING PLC','united kingdom','160','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','166','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','167','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','168','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','169','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pulse','PULSE HOMES PRODUCTS LTD','united kingdom','17','//Homes//Products','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','170','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LTD','united kingdom','171','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pulse','PULSE HOMES PRODUCTS LTD','united kingdom','18','//Homes//Products','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','181',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','182',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','183',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','184',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','185',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','186',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA LTD','united kingdom','187',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pulse','PULSE HOMES PRODUCTS LTD','united kingdom','19','//Homes//Products','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('w2008','W P P 2008 LTD','united kingdom','193',null,'//Ltd//PP',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('w2008','W P P 2008 LTD','united kingdom','194',null,'//Ltd//PP',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('w2008','W P P 2008 LTD','united kingdom','195',null,'//Ltd//PP',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','20','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','21','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','22','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','23','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','24','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','25','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM DESIGN BUILD LTD','united kingdom','26','//Build//Design','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','46','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','47','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','48','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','49','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','50','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','51','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','52','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','53','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LTD','united kingdom','54','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ikea','IKEA WHOLESALE LTD','united kingdom','80','//Wholesale','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ikea','IKEA WHOLESALE LTD','united kingdom','81','//Wholesale','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ikea','IKEA WHOLESALE LTD','united kingdom','82','//Wholesale','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ikea','IKEA WHOLESALE LTD','united kingdom','83','//Wholesale','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('meggitt','MEGGITT DEFENCE SYSTEMS LTD','united kingdom','98','//Defence//Systems','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE1 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('meggitt','MEGGITT DEFENCE SYSTEMS LTD','united kingdom','99','//Defence//Systems','//Ltd',null);
h2. Table 2
CREATE TABLE SCOTT."COMPANY_TABLE2"
( "ROOT" VARCHAR2(255 BYTE),
"COMPANY" VARCHAR2(255 BYTE),
"ST_COUNTRY" VARCHAR2(255 BYTE),
"ST_COID" VARCHAR2(255 BYTE),
"ACTIVITY" VARCHAR2(255 BYTE),
"ENTITY" VARCHAR2(255 BYTE),
"LOCATION" VARCHAR2(255 BYTE)
) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE SCOTT ;
REM INSERTING into SCOTT.COMPANY_TABLE2
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('aecom','AECOM LIMITED','united kingdom','27295',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA','united kingdom','28124',null,null,null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA PLC','united kingdom','28127',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK OPERATIONS LIMITED','united kingdom','30121','//Operations','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ge','GE HEALTHCARE UK LIMITED','united kingdom','33692','//Healthcare','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('ikea','IKEA LIMITED','united kingdom','35119',null,'//Ltd',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('meggitt','MEGGITT (UK) LTD','united kingdom','37651',null,'//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pilkington','PILKINGTON GROUP LTD','united kingdom','39290',null,'//Group//Ltd',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('pulse','PULSE HOME PRODUCTS','united kingdom','39757','//Homes//Products',null,null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('random','RANDOM HOUSE UK LIMITED','united kingdom','39883','//House','//Ltd','//United Kingdom');
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rankhovis','RANK HOVIS','united kingdom','39890',null,null,null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS-ROYCE PLC','united kingdom','40317',null,'//Plc',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('rollsroyce','ROLLS-ROYCE POWER ENGINEERING PLC','united kingdom','48178','//Engineering//Power','//Plc',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('w2008','W P P 2008 LIMITED','united kingdom','54441',null,'//Ltd//PP',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('cemex','CEMEX UK','united kingdom','60033',null,null,'//United Kingdom');
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('liverpool','LIVERPOOL DIRECT LIMITED','united kingdom','60614','//Direct','//Ltd',null);
Insert into SCOTT.COMPANY_TABLE2 (ROOT,COMPANY,ST_COUNTRY,ST_COID,ACTIVITY,ENTITY,LOCATION) values ('astrazeneca','ASTRAZENECA FINANCE LTD','united kingdom','98759','//Financial','//Ltd',null);
Edited by: user8788094 on 13-Jun-2011 05:07