トップページに戻る
次のmodel句のサンプルへ
前のmodel句のサンプルへ
model句18 nestedセル参照で、階層の根と葉の値を求める
SQLパズル
TreeTable
Code New_Code Name
---- -------- ----
1111 1111 AAAA
2222 2222 BBBB
3333 4444 CCCC
4444 4444 DDDD
5555 6666 EEEE
6666 7777 FFFF
7777 7777 GGGG
以下の、階層の根と葉の値を求めるクエリをmodel句で模倣する。
select connect_by_root Code as Code,
connect_by_root New_Code as New_Code,Name
from TreeTable
where connect_by_isleaf=1
connect by prior New_Code = Code
and prior RowID != RowID;
出力結果
Code New_Code Name
---- -------- ----
1 1 1111
2 2 2222
3 4 4444
4 4 4444
5 6 7777
6 7 7777
7 7 7777
データ作成スクリプト
create table TreeTable(Code,New_Code,Name) as
select 1111,1111,'AAAA' from dual union
select 2222,2222,'BBBB' from dual union
select 3333,4444,'CCCC' from dual union
select 4444,4444,'DDDD' from dual union
select 5555,6666,'EEEE' from dual union
select 6666,7777,'FFFF' from dual union
select 7777,7777,'GGGG' from dual;
SQL
select Code,New_Code,Name
from TreeTable
model
dimension by(Code)
measures(New_Code,Name)
rules ITERATE(100)
(Name[any] order by Code =
Name[New_Code[cv()]]);
解説