   null as bn_code, null as bn_id,
   null as mf_code, null as mf_id,
   null as quant_unit, null as quant_unit_id,
-  18 as prec
+  31 as prec
 from qr_ing
 where qi_code not in (select qi_combo from x_pattern)
 ;
 commit;
 
--- 19. and 20. All untranslatable are going to be added from extension_combo to x_pattern
+-- 32. and 33. All untranslatable are going to be added from extension_combo to x_pattern
 
 -- Create individual translation tables all starting with x_, with one best record for each q *
 -- Strategy: First use what's found in the patterns, then use precedences from relationship_to_concept and div between uds

    null as bn_code, null as bn_id,
    null as mf_code, null as mf_id,
    null as quant_unit, null as quant_unit_id,
    17 as prec
  from (
    select distinct 
      qi_combo, first_value (ri_combo) over (partition by qi_combo order by i_prec) as ri_combo
    from qr_i_combo
  -- Make sure it is not already covered
  -- compare to existing to make sure pattern isn't already covered
union
  select qi_combo, ri_combo
  from x_pattern
);
commit;

-- 18. Add single ingredient translations not found in the data, but provided by the input tables and have drugs containing them
insert /*+ APPEND */ into x_pattern
select distinct
  qi_code as qi_combo, first_value (ri_code) over (partition by qi_code order by prec) as ri_combo, -- pick the best translation

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