model句17 Partitioned Outer Joinもどき

SQLパズル

model句で、Partitioned Outer Joinもどきが可能です。
普通に、Partitioned Outer Joinを使うことが、ほとんどだと思いますが ;-)

create table modelTes(key,Val) as
select 1,10 from dual union all
select 1,20 from dual union all
select 2,10 from dual union all
select 3,10 from dual union all
select 3,30 from dual;

-----------------------------------------------------------------------------------
select文の結果が補完対象。

select * from modelTes
 model
 partition by(key)
 dimension by(Val)
 measures(0 as dummy)
 rules(
 dummy[for Val in(select * from table(sys.odciNumberList(10,20,30)))] = 0);

key  Val  dummy
---  ---  -----
  1   10  0
  1   20  0
  1   30  0
  2   10  0
  2   20  0
  2   30  0
  3   10  0
  3   30  0
  3   20  0

-----------------------------------------------------------------------------------
固定値が補完対象。
dummy[for Val in(10,20,30)]
は、位置参照なのでupsertされます。

select * from modelTes
 model
 partition by(key)
 dimension by(Val)
 measures(0 as dummy)
 rules(
 dummy[for Val in(10,20,30)] = 0);

key  Val  dummy
---  ---  -----
  1   10  0
  1   20  0
  1   30  0
  2   10  0
  2   20  0
  2   30  0
  3   10  0
  3   30  0
  3   20  0

-----------------------------------------------------------------------------------
これだと補完されません。
dummy[Val in(10,20,30)]
は、シンボリック参照だからです。

select * from modelTes
 model
 partition by(key)
 dimension by(Val)
 measures(0 as dummy)
 rules(dummy[Val in(10,20,30)] = 0);

key  Val  dummy
---  ---  -----
  1   10  0
  1   20  0
  2   10  0
  3   10  0
  3   30  0