oracle SQLにて、部品表の作成

Question

前提・実現したいこと

サンプルテーブルから、子部品を全て展開した状態にしたいです。

サンプルテーブル

親部品	子部品	生産場所
車	タイヤ	中国
タイヤ	バルブ	中国
バルブ	キャップ	中国
バルブ	シール	中国
バルブ	バルブコア	中国
キャップ	ゴム	中国

SQL取得結果として、下記のような半ストラクチャー状態にしたいと考えています。

親部品	中間完成品	子部品2	生産場所
車	タイヤ	バルブ	中国
車	タイヤ	キャップ	中国
車	タイヤ	シール	中国
車	タイヤ	バルブコア	中国
車	タイヤ	ゴム	中国

該当のソースコード

with BOM(親品番,子品番,生産場所)
as (select 親品番,子品番,生産場所 from サンプルテーブル)

select *
from BOM
left outer join サンプルテーブル ON サンプルテーブル.親品番 = BOM.子品番
where 親品番 like '車'

試したこと

該当のソースコードから、似たようなものを作成できましたが、
サンプルコードでいうバルブまでしか取得できませんでした・・。
バルブを更に展開した状態、キャップやシールといった部品も取得できるようにしたく思います。

方法が思いつかないものですから、
何か良い方法がございましたらご教示頂きたく、よろしくお願いいたします。
階層は最大5Lvと仮定しています。

Comments

[neko_the_shadow]

中間完成品の要件はどのようなものですか? たとえば車の直下にある部品を中間完成品と呼んでいますか? バルブやキャップは中間完成品になりますか?

[hk3150]

neko_the_shadow様
ご連絡ありがとうございます。
車の直下にある部品を中間完成品と呼んでいます。

BAに選ばせて頂きましたsazi様の回答にて無事解決に至りました。
この度は、私の為にお時間を使って頂きまして、ありがとうございました。

Answer 1

再帰になっていませんので当然ですね。

with bom(親品番, 子品番, 生産場所, 親部品, 中間完成品, lvl) as (
    select 親品番,子品番, 生産場所 , 親品番 as 親部品, 子品番 as 中間完成品, 1 
    from  sample
    where  親品番 like '車'
  union all
    select s.親品番, s.子品番, s.生産場所, b.親部品, b.中間完成品, b.lvl+1  
    from  bom b inner join sample s 
          on  b.子品番=s.親品番
)
select 親部品, 中間完成品, 子品番, 生産場所 
from bom 
where lvl > 1

因みに、階層のパンくずを表示するとしたら以下になります。

with bom(親品番, 子品番, 生産場所, 親部品, 中間完成品, 階層, lvl) as (
    select 親品番,子品番, 生産場所 , 親品番 as 親部品, 子品番 as 中間完成品
         , 親品番 || ' > '  || 子品番, 1 
    from  sample
    where  親品番 like '車'
  union all
    select s.親品番, s.子品番, s.生産場所, b.親部品, b.中間完成品
         , 階層 || ' > '  || s.子品番, b.lvl+1  
    from  bom b inner join sample s 
          on  b.子品番=s.親品番
)
select 親部品, 中間完成品, 子品番, 生産場所, 階層 
from bom 
where lvl > 1

Comments

[hk3150]

sazi様
ご教示頂きましたソースコードにて、無事できました；
いつもありがとうございます(_ _)