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()]]);

解説

nestedセル参照の使いどころかもしれませんが、
階層問い合わせを使うほうが、優れてる気がします。

OTN-Japan --- 階層の根と葉の値を求める

---