Reduce overload on pl/sql

Question

I have a requirement to do matching of few attributes one by one. I'm looking to avoid multiple select statements. Below is the example.

    Table1
    Col1|Price|Brand|size
    -----------------------
    A|10$|BRAND1|SIZE1
    B|10$|BRAND1|SIZE1
    C|30$|BRAND2|SIZE2
    D|40$|BRAND2|SIZE4


    Table2
    Col1|Col2|Col3
    --------------
    B|XYZ|PQR
    C|ZZZ|YYY


    Table3
    Col1|COL2|COL3|LIKECOL1|Price|brand|size
    -----------------------------------------
    B|XYZ|PQR|A|10$|BRAND1|SIZE1
    C|ZZZ|YYY|D|NULL|BRAND2|NULL

In table3, I need to insert data from table2 by checking below conditions.

1. Find a match for record in table2, if Brand and size, Price match
2. If no match found, then try just Brand, Size
3. still no match found, try brand only

In the above example, for the first record in table2, found match with all the 3 attributes and so inserted into table3 and second record, record 'D' is matching but only 'Brand'.

All I can think of is writing 3 different insert statements like below into an oracle pl/sql block.

 insert into table3 
   select from tab2 
    where all 3 attributes are matching;

 insert into table3 
   select from tab2 
    where brand and price are matching 
      and not exists in table3 (not exists is to avoid 
                                inserting the same record which was already 
                                inserted with all 3 attributes matched);

 insert into table3 
   select from tab2 
    where Brand is matching and not exists in table3;

Can anyone please suggest a better way to achieve it in any better way avoiding multiple times selecting from table2.

Answer 1

This is a case for OUTER APPLY.

OUTER APPLY is a type of lateral join that allows you join on dynamic views that refer to tables appearing earlier in your FROM clause. With that ability, you can define a dynamic view that finds all the matches, sorts them by the pecking order you've specified, and then use FETCH FIRST 1 ROW ONLY to only include the 1st one in the results.

Using OUTER APPLY means that if there is no match, you will still get the table B record -- just with all the match columns null. If you don't want that, you can change OUTER APPLY to CROSS APPLY.

Here is a working example (with step by step comments), shamelessly stealing the table creation scripts from Michael Piankov's answer:

create table Table1 (Col1,Price,Brand,size1)
as select 'A','10','BRAND1','SIZE1' from dual union all
   select 'B','10','BRAND1','SIZE1' from dual union all
   select 'C','30','BRAND2','SIZE2' from dual union all
   select 'D','40','BRAND2','SIZE4'from dual 

create table Table2(Col1,Col2,Col3)
as select 'B','XYZ','PQR' from dual union all 
   select'C','ZZZ','YYY' from dual;

-- INSERT INTO table3
SELECT t2.col1, t2.col2, t2.col3,
t1.col1 likecol1, 
decode(t1.price,t1_template.price,t1_template.price, null) price,
decode(t1.brand,t1_template.brand,t1_template.brand, null) brand,
decode(t1.size1,t1_template.size1,t1_template.size1, null) size1
FROM 
-- Start with table2
table2 t2
-- Get the row from table1 matching on col1... this is our search template
inner join table1 t1_template on
t1_template.col1 = t2.col1
-- Get the best match from table1 for our search 
-- template, excluding the search template itself
outer apply ( 
SELECT * FROM table1 t1 
WHERE 1=1
-- Exclude search template itself
and t1.col1 != t2.col1
-- All matches include BRAND
and t1.brand = t1_template.brand
-- order by match strength based on price and size
order by case when t1.price = t1_template.price and t1.size1 = t1_template.size1 THEN 1
when t1.size1 = t1_template.size1 THEN 2
else 3 END
-- Only get the best match for each row in T2
FETCH FIRST 1 ROW ONLY) t1;