     select distinct d_combo as rd_combo, df_id from r_existing 
   ) r using(rd_combo, df_id)
 union
-  select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, quant_unit, quant_unit_id
+  select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, quant_unit, quant_unit_id, 0 as new_rec
   from x_pattern where df_code is not null
 )
 left join x_unit on unit_code=quant_unit and unit_id=quant_unit_id

    select distinct i_combo as ri_combo, df_id from r_existing 
  ) r using(ri_combo, df_id)
union
  select qi_combo, ri_combo, df_code, df_id, dfg_id
  from x_pattern where df_code is not null
);
commit;

;
commit;

-- 3-6. Match d_combo, df, bn, but not mf - Branded Drug, quantified and boxed
insert /*+ APPEND */ into x_pattern
-- take out null values from union for performance
select qi_combo, ri_combo, qd_combo, rd_combo, df_code, df_id, dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id,
  case -- if the translation keeps the favorite quant_unit_id give it a better prec 
    when precedence is null then 3+new_rec-- solid drug
    when precedence=1 then 3+new_rec -- quant_unit and quant_unit_id match according to prec
  else 5+new_rec end as prec -- 5 if quant doesn't match
from (
  select
    qi_combo, first_value(ri_combo) over (partition by qd_combo, df_code, bn_code order by bn_prec, df_prec, div desc, i_prec, u_prec) as ri_combo,     
