drop table t1address_data ... ignore error if it does not exist.
drop table t1address_data
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table t1address_data
create table t1address_data
(
client_no varchar2(30) not null,
flat_no varchar2(7),
street_name varchar2(30),
building_name varchar2(30),
suburb varchar2(20),
create_date date not null
)
Table created.
insert test data in table
insert into t1address_data
With DQ1 as (
-- NOTE: Rows for clients #1 to #8 involve cases where same col is involved in the matching process.
-- For clients #1 to #8, 1 col is involved in the match to 1 or more patterns.
select 1,'PO BOX','Sturt Street','Paradise Tower','Tranmere', sysdate from dual
union all
select 2,'2A','PO BOX 123','Ocean Tower','Magill', sysdate from dual
union all
select 3,'30C','Jason Street','Pearl','Enfield', sysdate from dual
union all
select 4,'5A','Opal Street','View Apt','Parafield', sysdate from dual
union all
select 5,'7A','Pearl Street','Epoch Apt','Mayland', sysdate from dual
union all
select 6,'77A','Street','Trust Apt','box Prospect', sysdate from dual
union all
select 7,'07','Street','PO BOX 232',' Prospect', sysdate from dual
union all
select 8,'P.O','Street','Green Apt',' Prospect', sysdate from dual
-- NOTE: extra row added for client #9 to include a use case where multiple cols for same client are involved in the matching process.
-- For client #9, 2 cols are involved in the match to 1 or more patterns.
union all
select 9,'P.O','PO Street','APOLLO CREED Training Arena',' Prospect', sysdate from dual
)
SELECT * FROM DQ1
9 row(s) inserted.
commit
commit
Statement processed.
Take a look at the test data
select
t1addr.client_no, t1addr.flat_no, t1addr.street_name, t1addr.building_name, t1addr.suburb, t1addr.create_date
from t1address_data t1addr
| CLIENT_NO | FLAT_NO | STREET_NAME | BUILDING_NAME | SUBURB | CREATE_DATE | 1 | PO BOX | Sturt Street | Paradise Tower | Tranmere | 13-JUN-19 | 2 | 2A | PO BOX 123 | Ocean Tower | Magill | 13-JUN-19 | 3 | 30C | Jason Street | Pearl | Enfield | 13-JUN-19 | 4 | 5A | Opal Street | View Apt | Parafield | 13-JUN-19 | 5 | 7A | Pearl Street | Epoch Apt | Mayland | 13-JUN-19 | 6 | 77A | Street | Trust Apt | box Prospect | 13-JUN-19 | 7 | 07 | Street | PO BOX 232 | Prospect | 13-JUN-19 | 8 | P.O | Street | Green Apt | Prospect | 13-JUN-19 | 9 | P.O | PO Street | APOLLO CREED Training Arena | Prospect | 13-JUN-19 |
|---|
Statement 6. Main Query to test sql pattern matching ... Replace the last line as explained by output of statement 7 and view results.
WITH
filterData as (
-- put in all the patterns that are to be searched across columns
SELECT 1 as fltrOrd, 'PO ' AS fltrDesc from dual
UNION ALL
SELECT 2, 'BOX' from dual
UNION ALL
SELECT 3, 'PO BOX' from dual
UNION ALL
SELECT 4, 'P.' from dual
),
addrUnpivot as (
-- Convert the address columns to rows to allow searching across patterns. Define column order.
select
client_no,
create_date,
case addrCol when 'FLAT_NO' then 1 when 'STREET_NAME' then 2 when 'BUILDING_NAME' then 3 when 'SUBURB' then 4 end as addrColOrd,
case addrCol when 'FLAT_NO' then 'Flat' when 'STREET_NAME' then 'Street' when 'BUILDING_NAME' then 'Building' when 'SUBURB' then 'Suburb' end as addrColNm,
addrCol,
addrColVal
from t1address_data t1addr
unpivot( addrColVal for addrCol in (
flat_no as 'FLAT_NO',
street_name as 'STREET_NAME',
building_name as 'BUILDING_NAME',
suburb as 'SUBURB'
))
where 1=1
order by client_no, case addrCol when 'FLAT_NO' then 1 when 'STREET_NAME' then 2 when 'BUILDING_NAME' then 3 when 'SUBURB' then 4 end
),
addrUnpivotFltr as (
-- Join Address column data (as rows) with original data to store address columns as "input" attributes also
-- Join with filters to get only matching addrColumn-filter combinations
select
addrUnp.client_no,
addrUnp.create_date,
t1addr.flat_no as flat_no_inp, t1addr.street_name as street_name_inp, t1addr.building_name as building_name_inp, t1addr.suburb as suburb_inp,
addrUnp.addrColOrd,
addrUnp.addrColNm,
addrUnp.addrCol,
addrUnp.addrColVal,
fltr.fltrOrd, fltr.fltrDesc
from addrUnpivot addrUnp INNER JOIN t1address_data t1addr on (addrUnp.client_no = t1addr.client_no)
INNER JOIN filterData fltr on (instr(ltrim(rtrim(upper(addrUnp.addrColVal))), fltr.fltrDesc) > 0)
order by addrUnp.client_no, addrUnp.addrColOrd, fltr.fltrOrd
),
MR1stMatchPerClientQ1 as (
--
-- MATCH_RECOGNIZE Query Example1
--
-- NOTE:
-- ONE ROW PER MATCH
-- Gets summary info about matching records
-- Greedy pattern used to match so that we only get 1 match per client_no
--
SELECT
--anyMatch
MRAddr.client_no as client_no,
MRAddr.create_date_op as create_date,
coalesce(case when MRAddr.matchCol_op = 'FLAT_NO' then MRAddr.matchColVal_op end, MRAddr.flat_no_op) as FLAT_NO,
coalesce(case when MRAddr.matchCol_op = 'STREET_NAME' then MRAddr.matchColVal_op end, MRAddr.street_name_op) as STREET_NAME,
coalesce(case when MRAddr.matchCol_op = 'BUILDING_NAME' then MRAddr.matchColVal_op end, MRAddr.building_name_op) as BUILDING_NAME,
coalesce(case when MRAddr.matchCol_op = 'SUBURB' then MRAddr.matchColVal_op end, MRAddr.suburb_op) as SUBURB,
MRAddr.addrCol_op as addrCol,
MRAddr.addrColOrd_op as addrColOrd,
MRAddr.addrColNm_op as addrColNm,
MRAddr.addrColVal_op as addrColVal,
MRAddr.matchFltrOrd_op as fltrOrd,
'"' || MRAddr.matchFltrDesc_op || '"' as fltrDesc
--, MRAddr.mn_op0 as mn, MRAddr.cl_op0 as cl
FROM addrUnpivotFltr
MATCH_RECOGNIZE (
PARTITION by client_no
ORDER by addrColOrd, fltrOrd
MEASURES
final first(anyMatch.create_date) as create_date_op,
final first(anyMatch.flat_no_inp) as flat_no_op,
final first(anyMatch.street_name_inp) as street_name_op,
final first(anyMatch.building_name_inp) as building_name_op,
final first(anyMatch.suburb_inp) as suburb_op,
final first(anyMatch.addrCol) as addrCol_op,
final first(anyMatch.addrColNm) as addrColNm_op,
final first(anyMatch.addrColOrd) as addrColOrd_op,
final first(anyMatch.addrColVal) as addrColVal_op,
--
final first(anyMatch.addrCol) as matchCol_op,
final first(anyMatch.addrColOrd) as matchColOrd_op,
final first(anyMatch.addrColNm) as matchColNm_op,
final first(anyMatch.addrColVal) as matchColVal_op,
--
final first(anyMatch.fltrOrd) as matchFltrOrd_op,
final first(anyMatch.fltrDesc) as matchFltrDesc_op
--,
--match_number() as mn_op0,
--classifier() as cl_op0
--ALL ROWS PER MATCH
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( anyMatch* )
DEFINE
anyMatch as fltrDesc = 'PO ' or
fltrDesc = 'BOX' or
fltrDesc = 'PO BOX' or
fltrDesc = 'P.'
) MRAddr
where 1=1
),
MRMultipleRowsPerClientQ1 as (
--
-- MATCH_RECOGNIZE Query Example2
--
-- NOTE:
-- ALL ROWS PER MATCH
-- Gets detailed info about all matching records
-- Greedy pattern used to match so that we only get 1 match per client_no
--
SELECT
--anyMatch
MRAddr.client_no as client_no,
MRAddr.create_date_op as create_date,
coalesce(case when MRAddr.addrCol_op = 'FLAT_NO' then MRAddr.addrColVal_op end, MRAddr.flat_no_op) as FLAT_NO,
coalesce(case when MRAddr.addrCol_op = 'STREET_NAME' then MRAddr.addrColVal_op end, MRAddr.street_name_op) as STREET_NAME,
coalesce(case when MRAddr.addrCol_op = 'BUILDING_NAME' then MRAddr.addrColVal_op end, MRAddr.building_name_op) as BUILDING_NAME,
coalesce(case when MRAddr.addrCol_op = 'SUBURB' then MRAddr.addrColVal_op end, MRAddr.suburb_op) as SUBURB,
MRAddr.addrCol_op as addrCol,
MRAddr.addrColOrd_op as addrColOrd,
MRAddr.addrColNm_op as addrColNm,
MRAddr.addrColVal_op as addrColVal,
MRAddr.matchFltrOrd_op as fltrOrd,
'"' || MRAddr.matchFltrDesc_op || '"' as fltrDesc,
--
--ADWC is on 18c (Pre-19c) ... no listagg distinct. Hence client_match_cols will repeat in ADWC or pre-19c.
--
--listagg(to_char(MRAddr.matchFltrOrd_op), ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.matchFltrOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_fltrs_ord,
--listagg('"' || MRAddr.matchFltrDesc_op || '"', ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.matchFltrOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_fltrs,
--listagg(to_char(MRAddr.addrColOrd_op), ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols_ord,
--listagg(MRAddr.addrCol_op, ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols,
--listagg(MRAddr.addrColVal_op, ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols_val
--
--livesql is on 19c which has listagg distinct. client_match_cols will not repeat in livesql (19c).
--
listagg(distinct to_char(MRAddr.matchFltrOrd_op), ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.matchFltrOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_fltrs_ord,
listagg(distinct '"' || MRAddr.matchFltrDesc_op || '"', ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.matchFltrOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_fltrs,
listagg(distinct to_char(MRAddr.addrColOrd_op), ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols_ord,
listagg(distinct MRAddr.addrCol_op, ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols,
listagg(distinct MRAddr.addrColVal_op, ',') WITHIN GROUP (ORDER by MRAddr.rc asc,MRAddr.addrColOrd_op) OVER (PARTITION BY MRAddr.client_no) as client_match_cols_val
-- ,rc, MRAddr.mn_op0 as mn, MRAddr.cl_op0 as cl
FROM addrUnpivotFltr
MATCH_RECOGNIZE (
PARTITION by client_no
ORDER by addrColOrd, fltrOrd
MEASURES
max(anyMatch.create_date) as create_date_op,
max(anyMatch.flat_no_inp) as flat_no_op,
max(anyMatch.street_name_inp) as street_name_op,
max(anyMatch.building_name_inp) as building_name_op,
max(anyMatch.suburb_inp) as suburb_op,
max(anyMatch.addrCol) as addrCol_op,
anyMatch.addrColNm as addrColNm_op,
anyMatch.addrColOrd as addrColOrd_op,
anyMatch.addrColVal as addrColVal_op,
--
anyMatch.fltrOrd as matchFltrOrd_op,
anyMatch.fltrDesc as matchFltrDesc_op,
sum(1) as rc
--,
--match_number() as mn_op0,
--classifier() as cl_op0
ALL ROWS PER MATCH
--ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( anyMatch* )
DEFINE
anyMatch as fltrDesc = 'PO ' or
fltrDesc = 'BOX' or
fltrDesc = 'PO BOX' or
fltrDesc = 'P.'
) MRAddr
where 1=1
),
MRSummRowPerClientQ1 as (
--shrink multiple rows per client to single (select and group by summary fields)
select
client_no,
create_date,
FLAT_NO, STREET_NAME, BUILDING_NAME, SUBURB,
--ADWC is on 18c (Pre-19c) ... no listagg distinct. Hence client_match_cols will repeat in ADWC or pre-19c.
max(MRMRPCQ1.client_match_fltrs_ord) as client_match_fltrs_ord,
max(MRMRPCQ1.client_match_fltrs) as client_match_fltrs,
max(MRMRPCQ1.client_match_cols_ord) as client_match_cols_ord,
max(MRMRPCQ1.client_match_cols) as client_match_cols,
max(MRMRPCQ1.client_match_cols_val) as client_match_cols_val
from MRMultipleRowsPerClientQ1 MRMRPCQ1
where 1=1
group by
client_no,
create_date,
FLAT_NO, STREET_NAME, BUILDING_NAME, SUBURB
order by 1,2
)
/* .... select * from MR1stMatchPerClientQ1 */
/* .... select * from t1address_data */
/* .... select * from addrUnpivot */
/* .... select * from addrUnpivotFltr */
/* .... select * from MR1stMatchPerClientQ1 */
/* .... select * from MRMultipleRowsPerClientQ1 */
/* .... select * from MRSummRowPerClientQ1 */
select * from MR1stMatchPerClientQ1
| CLIENT_NO | CREATE_DATE | FLAT_NO | STREET_NAME | BUILDING_NAME | SUBURB | ADDRCOL | ADDRCOLORD | ADDRCOLNM | ADDRCOLVAL | FLTRORD | FLTRDESC | 1 | 13-JUN-19 | PO BOX | Sturt Street | Paradise Tower | Tranmere | FLAT_NO | 1 | Flat | PO BOX | 1 | "PO " | 2 | 13-JUN-19 | 2A | PO BOX 123 | Ocean Tower | Magill | STREET_NAME | 2 | Street | PO BOX 123 | 1 | "PO " | 6 | 13-JUN-19 | 77A | Street | Trust Apt | box Prospect | SUBURB | 4 | Suburb | box Prospect | 2 | "BOX" | 7 | 13-JUN-19 | 07 | Street | PO BOX 232 | Prospect | BUILDING_NAME | 3 | Building | PO BOX 232 | 1 | "PO " | 8 | 13-JUN-19 | P.O | Street | Green Apt | Prospect | FLAT_NO | 1 | Flat | P.O | 4 | "P." | 9 | 13-JUN-19 | P.O | PO Street | APOLLO CREED Training Arena | Prospect | FLAT_NO | 1 | Flat | P.O | 4 | "P." |
|---|
Further Instructions to understand script logic ... repeat Statement 6 modifying the sql as per Instructions given ...
With FurtherInstructionsQ as (
select 1 seq, 'Default output: 1st Match of addrCol-Fltr ... addrCol1 against all fltrs, then addrCol2 against all fltrs, etc' as output, '(i) In last line of sql statement #6, use MR1stMatchPerClientQ1, (ii) verify that client #9 is matched to column #1 FLAT with filter #4 "P."' Instructions
from dual
UNION ALL
select 2, 'addrCols data', '(i) In last line of sql statement #6, Replace with t1address_data'
from dual
UNION ALL
select 3, 'addrCols pivoted as rows', '(i) In last line of sql statement #6, Replace with addrUnpivot'
from dual
UNION ALL
select 4, 'pivoted data matched to all fltrs', '(i) In last line of sql statement #6, Replace with addrUnpivotFltr'
from dual
UNION ALL
select 5, '1st Match of addrCol-Fltr ... addrCol1 against all fltrs, then addrCol2 against all fltrs, etc', '(i) In last line of sql statement #6, Replace with MR1stMatchPerClientQ1, (ii) verify that client #9 is matched to column #1 FLAT with filter #4 "P."'
from dual
UNION ALL
select 6, 'All Matches of addrCol-Fltr', '(i) In last line of sql statement #6, Replace with MRMultipleRowsPerClientQ1, (ii) Note that usage of distinct in LISTAGG avoids repetition of match details in summary column(s) client_match_cols, (iii) For pre-19c (non-livesql) Db, please remove distinct from listagg and retry. Match details will repeat unnecessarily.'
from dual
UNION ALL
select 7, 'Single Row Summarizing all Matches of addrCol-Fltr', '(i) In last line of sql statement #6, Replace with MRSummRowPerClientQ1, (ii) Note that usage of distinct in LISTAGG avoids repetition of match details in column(s) client_match_cols, (iii) For pre-19c (non-livesql) Db, please remove distinct from listagg and retry. Match details will repeat unnecessarily.'
from dual
UNION ALL
select 8, '1st Match of Fltr-addrCol ... Fltr1 against all cols, then Fltr2 against all cols, etc', '(i) In query block MR1stMatchPerClientQ1, MATCH_RECOGNIZE clause ... reverse order of columns ... use "ORDER by fltrOrd, addrColOrd", (ii) In last line of sql statement #6, Replace with MR1stMatchPerClientQ1, (iii) verify that client #9 is now matched using column #2 STREET_NAME and filter #1 "PO "'
from dual
)
select * from FurtherInstructionsQ
| SEQ | OUTPUT | INSTRUCTIONS | 1 | Default output: 1st Match of addrCol-Fltr ... addrCol1 against all fltrs, then addrCol2 against all fltrs, etc | (i) In last line of sql statement #6, use MR1stMatchPerClientQ1, (ii) verify that client #9 is matched to column #1 FLAT with filter #4 "P." | 2 | addrCols data | (i) In last line of sql statement #6, Replace with t1address_data | 3 | addrCols pivoted as rows | (i) In last line of sql statement #6, Replace with addrUnpivot | 4 | pivoted data matched to all fltrs | (i) In last line of sql statement #6, Replace with addrUnpivotFltr | 5 | 1st Match of addrCol-Fltr ... addrCol1 against all fltrs, then addrCol2 against all fltrs, etc | (i) In last line of sql statement #6, Replace with MR1stMatchPerClientQ1, (ii) verify that client #9 is matched to column #1 FLAT with filter #4 "P." | 6 | All Matches of addrCol-Fltr | (i) In last line of sql statement #6, Replace with MRMultipleRowsPerClientQ1, (ii) Note that usage of distinct in LISTAGG avoids repetition of match details in summary column(s) client_match_cols, (iii) For pre-19c (non-livesql) Db, please remove distinct from listagg and retry. Match details will repeat unnecessarily. | 7 | Single Row Summarizing all Matches of addrCol-Fltr | (i) In last line of sql statement #6, Replace with MRSummRowPerClientQ1, (ii) Note that usage of distinct in LISTAGG avoids repetition of match details in column(s) client_match_cols, (iii) For pre-19c (non-livesql) Db, please remove distinct from listagg and retry. Match details will repeat unnecessarily. | 8 | 1st Match of Fltr-addrCol ... Fltr1 against all cols, then Fltr2 against all cols, etc | (i) In query block MR1stMatchPerClientQ1, MATCH_RECOGNIZE clause ... reverse order of columns ... use "ORDER by fltrOrd, addrColOrd", (ii) In last line of sql statement #6, Replace with MR1stMatchPerClientQ1, (iii) verify that client #9 is now matched using column #2 STREET_NAME and filter #1 "PO " |
|---|
Some background to Soln: Why convert addr data from columns to rows? Sql pattern matching with input data as is can lead to client #9 being reported twice for two matches. Its difficult to prioritise between addrCol-fltr matches for same client.
With filter_data as (
-- put in all the patterns that are to be searched across columns
SELECT 1 as fltr_order, 'PO ' AS fltr_desc from dual
UNION ALL
SELECT 2, 'BOX' from dual
UNION ALL
SELECT 3, 'PO BOX' from dual
UNION ALL
SELECT 4, 'P.' from dual
),
addr_fltr as (
select
t1addr.client_no, t1addr.flat_no, t1addr.street_name, t1addr.building_name, t1addr.suburb, t1addr.create_date,
fltr.fltr_order, fltr.fltr_desc
from t1address_data t1addr INNER JOIN filter_data fltr on ( 1=1 ) --cross join to match input data against all possible patterns
order by t1addr.client_no, t1addr.create_date, fltr.fltr_order
),
--
-- MATCH_RECOGNIZE Query Example 3
--
-- Change (Comment/Uncomment) the last 2 lines to switch between multiple rows per Client to single row per Client
--
--
--
MRMultiRowsPerClientQ1 as (
select
MRAddr.client_no_op as client_no, MRAddr.flat_no_op as flat_no, MRAddr.street_name_op as street_name, MRAddr.building_name_op as building_name, MRAddr.suburb_op as suburb, MRAddr.create_date_op as create_date,
MRAddr.fltr_order_op as fltr_order, MRAddr.fltr_desc_op as fltr_desc,
coalesce(MRAddr.fltr_col_flat, MRAddr.fltr_col_street, MRAddr.fltr_col_bldg, MRAddr.fltr_col_suburb) as client_match_col,
MRAddr.fltr_desc_op as client_match_fltr_desc,
sum(1) over (PARTITION BY MRAddr.client_no_op) as client_match_cnt,
first_value(MRAddr.fltr_desc_op) OVER (PARTITION BY MRAddr.client_no_op) as client_match_1st_fltr,
first_value(coalesce(MRAddr.fltr_col_flat, MRAddr.fltr_col_street, MRAddr.fltr_col_bldg, MRAddr.fltr_col_suburb)) OVER (PARTITION BY MRAddr.client_no_op) as client_match_1st_col,
--
--ADWC is on 18c (Pre-19c) ... no listagg distinct. Hence client_match_cols will repeat in ADWC or pre-19c.
--
--listagg(MRAddr.fltr_desc_op, ',') WITHIN GROUP (ORDER by MRAddr.fltr_order_op) OVER (PARTITION BY MRAddr.client_no_op) as client_match_fltrs,
--listagg(coalesce(MRAddr.fltr_col_flat, MRAddr.fltr_col_street, MRAddr.fltr_col_bldg, MRAddr.fltr_col_suburb), ',') WITHIN GROUP (ORDER by MRAddr.fltr_order_op) OVER (PARTITION BY MRAddr.client_no_op) as client_match_cols
--
--livesql is on 19c which has listagg distinct. client_match_cols will not repeat in livesql (19c).
--
listagg(distinct MRAddr.fltr_desc_op, ',') WITHIN GROUP (ORDER by MRAddr.fltr_order_op) OVER (PARTITION BY MRAddr.client_no_op) as client_match_fltrs,
listagg(distinct coalesce(MRAddr.fltr_col_flat, MRAddr.fltr_col_street, MRAddr.fltr_col_bldg, MRAddr.fltr_col_suburb), ',') WITHIN GROUP (ORDER by MRAddr.fltr_order_op) OVER (PARTITION BY MRAddr.client_no_op) as client_match_cols
--, MRAddr.rc
--, MRAddr.mn, MRAddr.cl
from addr_fltr
MATCH_RECOGNIZE (
PARTITION by client_no
ORDER by create_date, fltr_order
MEASURES
client_no as client_no_op,
flat_no as flat_no_op,
street_name as street_name_op,
building_name as building_name_op,
suburb as suburb_op,
create_date as create_date_op,
fltr_order as fltr_order_op,
fltr_desc as fltr_desc_op,
case when FlatNofltr.client_no is not null then 'FLAT_NO' end as fltr_col_flat,
case when StreetNamefltr.client_no is not null then 'STREET_NAME' end as fltr_col_street,
case when BuildingNamefltr.client_no is not null then 'BUILDING_NAME' end as fltr_col_bldg,
case when Suburbfltr.client_no is not null then 'SUBURB' end as fltr_col_suburb
--,
--sum(1) as rc,
--match_number() as mn,
--classifier() as cl
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ((FlatNofltr | StreetNamefltr | BuildingNamefltr | Suburbfltr)+)
DEFINE
FlatNofltr as instr(ltrim(rtrim(upper(flat_no))), fltr_desc) > 0,
StreetNamefltr as instr(ltrim(rtrim(upper(street_name))), fltr_desc) > 0,
BuildingNamefltr as instr(ltrim(rtrim(upper(building_name))), fltr_desc) > 0,
Suburbfltr as instr(ltrim(rtrim(upper(suburb))), fltr_desc) > 0
) MRAddr
where 1=1
),
MROneRowPerClientQ1 as
(
select
MRCQ1.client_no, MRCQ1.flat_no, MRCQ1.street_name, MRCQ1.building_name, MRCQ1.suburb, MRCQ1.create_date,
MRCQ1.client_match_col, client_match_fltr_desc,
max(MRCQ1.client_match_1st_fltr) as client_match_1st_fltr,
max(MRCQ1.client_match_1st_col) as client_match_1st_col,
max(MRCQ1.client_match_cnt) as client_match_cnt,
MAX(MRCQ1.client_match_fltrs) as client_match_fltrs,
MAX(MRCQ1.client_match_cols) as client_match_cols
from MRMultiRowsPerClientQ1 MRCQ1
group by
MRCQ1.client_no, MRCQ1.flat_no, MRCQ1.street_name, MRCQ1.building_name, MRCQ1.suburb, MRCQ1.create_date,
MRCQ1.client_match_col, client_match_fltr_desc
order by 1
)
SELECT * FROM MROneRowPerClientQ1
| CLIENT_NO | FLAT_NO | STREET_NAME | BUILDING_NAME | SUBURB | CREATE_DATE | CLIENT_MATCH_COL | CLIENT_MATCH_FLTR_DESC | CLIENT_MATCH_1ST_FLTR | CLIENT_MATCH_1ST_COL | CLIENT_MATCH_CNT | CLIENT_MATCH_FLTRS | CLIENT_MATCH_COLS | 1 | PO BOX | Sturt Street | Paradise Tower | Tranmere | 13-JUN-19 | FLAT_NO | PO BOX | PO BOX | FLAT_NO | 1 | PO BOX | FLAT_NO | 2 | 2A | PO BOX 123 | Ocean Tower | Magill | 13-JUN-19 | STREET_NAME | PO BOX | PO BOX | STREET_NAME | 1 | PO BOX | STREET_NAME | 6 | 77A | Street | Trust Apt | box Prospect | 13-JUN-19 | SUBURB | BOX | BOX | SUBURB | 1 | BOX | SUBURB | 7 | 07 | Street | PO BOX 232 | Prospect | 13-JUN-19 | BUILDING_NAME | PO BOX | PO BOX | BUILDING_NAME | 1 | PO BOX | BUILDING_NAME | 8 | P.O | Street | Green Apt | Prospect | 13-JUN-19 | FLAT_NO | P. | P. | FLAT_NO | 1 | P. | FLAT_NO | 9 | P.O | PO Street | APOLLO CREED Training Arena | Prospect | 13-JUN-19 | FLAT_NO | P. | PO | STREET_NAME | 2 | PO ,P. | STREET_NAME,FLAT_NO | 9 | P.O | PO Street | APOLLO CREED Training Arena | Prospect | 13-JUN-19 | STREET_NAME | PO | PO | STREET_NAME | 2 | PO ,P. | STREET_NAME,FLAT_NO |
|---|