model句20 rules句で、case式で場合分け

SQLパズル

Seq  Flag
---  ----
1    AAAA    ←出力対象
2    AAAA    ←出力対象
3    BBBB    ←出力対象
4    BBBB    ←出力対象
5    CCCC    ←出力対象
6    AAAA    ←出力対象
7    BBBB    ←出力対象
8    CCCC    ←出力対象
9    AAAA    ←出力対象
10   AAAA    ←出力対象
11   CCCC    ←出力対象
12   CCCC
13   BBBB
14   AAAA    ←出力対象
15   AAAA    ←出力対象
16   BBBB    ←出力対象
17   CCCC    ←出力対象
18   BBBB

Seqの昇順で、
AAAAの行から、CCCCの行まで出力する。ことを繰り返す。

出力結果
Seq  Flag
---  ----
1    AAAA
2    AAAA
3    BBBB
4    BBBB
5    CCCC
6    AAAA
7    BBBB
8    CCCC
9    AAAA
10   AAAA
11   CCCC
14   AAAA
15   AAAA
16   BBBB
17   CCCC

データ作成スクリプト

create table SeqTable(ID,Flag) as
select  1,'AAAA' from dual union
select  2,'AAAA' from dual union
select  3,'BBBB' from dual union
select  4,'BBBB' from dual union
select  5,'CCCC' from dual union
select  6,'AAAA' from dual union
select  7,'BBBB' from dual union
select  8,'CCCC' from dual union
select  9,'AAAA' from dual union
select 10,'AAAA' from dual union
select 11,'CCCC' from dual union
select 12,'CCCC' from dual union
select 13,'BBBB' from dual union
select 14,'AAAA' from dual union
select 15,'AAAA' from dual union
select 16,'BBBB' from dual union
select 17,'CCCC' from dual union
select 18,'BBBB' from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select ID,Flag
from (select ID,Flag,willOut
        from SeqTable
       model
       dimension by(ID)
       measures(Flag,0 as willOut)
       rules(
       willOut[ID] order by ID =
       case when Flag[cv()] = 'AAAA' then 1
            when presentv(Flag[cv()-1],1,0) = 0 then 0
            when Flag[cv()-1] = 'CCCC' then 0
            else willOut[cv()-1] end))
 where willOut = 1;

--■■■表関数を使う方法■■■
create or replace package packModel20 Is
    type PrintType is record(
    ID   SeqTable.ID%type,
    Flag SeqTable.Flag%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintRecord return packModel20.PrintTypeSet PipeLined IS
    out_rec packModel20.PrintType;
    willOut boolean := false;
begin
    for rec in (select ID,Flag
                  from SeqTable
                order by ID) Loop
        if rec.Flag = 'AAAA' then
            willOut := true;
        end if;
        if willOut then
            out_rec.ID   := rec.ID;
            out_rec.Flag := rec.Flag;
            pipe row(out_rec);
        end if;
        if rec.Flag = 'CCCC' then
            willOut := false;
        end if;
    end loop;
end;
/

sho err

select ID,Flag from table(PrintRecord);

解説

@IT会議室のアレンジ問題です。

model句を使う方法では、case式で場合分けのロジックを使ってます。
なぜ場合分けが必要か

表関数を使うのもいいでしょう。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
10.2.0.1.0でcase式で、not is presentを使ったら、
ORA-03113: が出ました・・・
"case when not m[1] is present then" outputs ORA-03113

select * from dual model
 dimension by(0 as d)
 measures(1 as m)
 rules(
 m[any] = case when not m[1] is present then 3 end);
ORA-03113: 通信チャネルでend-of-fileが検出されました

なので、presentvで代用するといいでしょう。
select * from dual model
 dimension by(0 as d)
 measures(1 as m)
 rules(
 m[any] = case when presentv(m[1],1,0) = 0 then 3 end);

D  M
-  -
0  3

---