 ;
 commit;
 
+-- Break up multi-combos
+insert /*+ APPEND */ into x_pattern
+select 
+  qi_combo, ri_combo, qd_combo, rd_combo, null as df_code, null as df_id, null as dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id, prec
+from (
+  select distinct 
+    q.qi_combo, r.ri_combo, 
+    cast(q_ds as varchar2(20)) as qd_combo, cast(r_ds as varchar2(20)) as rd_combo, 
+    x.bn_code, x.bn_id, 
+    x.quant_unit, x.quant_unit_id,
+    prec -- as already exists
+  from ( -- break up all combos for q
+    select distinct d_combo as qd_combo, i_code as qi_combo, ds_code as q_ds from q_ds join q_combo using(concept_code) where d_combo like '%-%'
+  ) q
+  join x_pattern x using(qd_combo) -- find them in the existing complete p to existing r 
+  join ( -- break up all combos for r
+    select distinct d_combo as rd_combo, i_code as ri_combo, ds_code as r_ds from r_ds join r_combo using(concept_id) where d_combo like '%-%'
+  ) r using(rd_combo) 
+  join qr_uds using(q_ds, r_ds) -- get right component in a combination aligned
+minus
+  select qi_combo, ri_combo, qd_combo, rd_combo, bn_code, bn_id, quant_unit, quant_unit_id, prec
+  from x_pattern
+);
+commit;
+
 -- 11 and 12. Match d_combo, but not df, bn, mf - Clinical Component
 insert /*+ APPEND */ into x_pattern
 select qi_combo, ri_combo, qd_combo, rd_combo, null as df_code, null as df_id, null as dfg_id, null as bn_code, null as bn_id, null as mf_code, null as mf_id, quant_unit, quant_unit_id, 


-- Create sequence that starts after existing OMOP???-style concept codes
drop sequence omop_seq;
-- /* XXXX uncomment
declare
 ex number;
begin

);
commit;

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'x_pattern', estimate_percent  => null, cascade  => true);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'qr_i', estimate_percent  => null, cascade  => true);

-- Break up mulit-i_combos and write back leaving all other patterns unchanged
insert /*+ APPEND */ into x_pattern
  select qi_combo, ri_combo, null as qd_combo, null as rd_combo, df_code, df_id, dfg_id, bn_code, bn_id, null as mf_code, null as mf_id, null as quant_unit, null as quant_unit_id, prec
from (
  select 
    qi_combo, ri_combo, 
    x.df_code, x.df_id, x.dfg_id,
    x.bn_code, x.bn_id,
    x.prec
  from x_pattern x
  join ( -- break up qi_combo from actual combo code, there is no easy way of doing it similar to q_ds
    select qi_combo, trim(regexp_substr(qi_combo, '[^\-]+', 1, levels.column_value)) as qi_code
    from (select distinct qi_combo from x_pattern), -- extension_combo contains i_combos as well
    table(cast(multiset(select level from dual connect by level <= length (regexp_replace(qi_combo, '[^\-]+'))  + 1) as sys.OdciNumberList)) levels
  ) q using(qi_combo)
  join ( -- break up ri_combo
    select ri_combo, trim(regexp_substr(ri_combo, '[^\-]+', 1, levels.column_value)) as ri_code
    from (select distinct ri_combo from x_pattern), -- extension_combo contains i_combos as well
    table(cast(multiset(select level from dual connect by level <= length (regexp_replace(ri_combo, '[^\-]+'))  + 1) as sys.OdciNumberList)) levels
  ) r using(ri_combo)
  join qr_i using(qi_code, ri_code) -- get right component in a combination aligned
  where qd_combo is null -- only Form patterns
minus
  select qi_combo, ri_combo, df_code, df_id, dfg_id, bn_code, bn_id, prec
  from x_pattern
);
commit;

-- 14. Match i_combo, df but not bn, mf - Clinical Forms
insert /*+ APPEND */ into x_pattern
select qi_combo, ri_combo, null as qd_combo, null as rd_combo, df_code, df_id, dfg_id, 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, 14 as prec
