MR use case sql for multi cols matching against multi patterns

View All Scripts Login to Run Script

Script Name MR use case sql for multi cols matching against multi patterns
Description Match_Recognize SQL use case to match multiple columns against multiple patterns ... Reference: https://www.linkedin.com/pulse/filtering-data-multiple-patterns-across-columns-sql-swanand-marathe/
Area SQL Analytics
Contributor Shankar Somayajula (Oracle)
Created Friday May 31, 2019

Statement 1
drop table t1address_data ... ignore error if it does not exist.
```
drop table t1address_data
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

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.

Statement 3

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.

Statement 4
commit
```
commit
```
```
Statement processed.
```

Statement 5

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

9 rows selected.

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

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."

6 rows selected.

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."

Statement 7

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 "

8 rows selected.

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 "

Statement 8

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

7 rows selected.

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database