Oracleの階層問い合わせで計算値を子に引き継ぎたい

Question

Oracleの階層問い合わせで、親の数量と親に対する数量を元に子の数量を順次計算していくことは可能でしょうか？
以下のようなテーブルで最上位の数量が変更された場合、子の数量を再計算したいです。

• 更新前

ID	親ID	親に対する必要数	数量
ID1		2
ID2	ID1	2	4
ID3	ID2	3	12
ID4	ID2	1	4

• 更新後（ID1の数量を2→1に更新）

ID	親ID	親に対する必要数	数量
ID1		1
ID2	ID1	2	4
ID3	ID2	3	12
ID4	ID2	1	4

• 更新後テーブルから求めたい結果

親の数量*親に対する必要数で子の数量を再計算

ID	親ID	親に対する必要数	数量(求めたい値)	備考
ID1		1	最上位
ID2	ID1	2	2	1(親数量)*2(親に対する必要数)
ID3	ID2	3	6	2(親数量)*3(親に対する必要数)
ID4	ID2	1	2	2(親数量)*1(親に対する必要数)

どなたかよい方法があればご教示お願い致します。
以下のSQLがやりたいことに近いのですが、最上位の子までしか計算できません。

SELECT DISTINCT
  LEVEL
  , ID
  , ID_PARENT
  , RT_PARENT 親に対する必要数
  , VL_ITEM 数量
  , (PRIOR VL_ITEM * RT_PARENT) 計算後数量 
FROM
  TEST 
START WITH
  ID = 'ID1' 
CONNECT BY
  PRIOR ID = ID_PARENT;

-- CREATE
create table TEST (
  ID VARCHAR2(5)
  , ID_PARENT VARCHAR2(5)
  , VL_ITEM NUMBER(4,0)
  , RT_PARENT NUMBER(4,0)
  , constraint TEST_PKC primary key (ID)
) ;

comment on column TEST.ID is 'ID';
comment on column TEST.ID_PARENT is '親ID';
comment on column TEST.VL_ITEM is '数量';
comment on column TEST.RT_PARENT is '親に対する必要数';

-- INSERT
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID1',null,1,null);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID2','ID1',4,2);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID3','ID2',12,3);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID4','ID2',4,1);

Comments

[Orlofsky]

テーブル定義とデータは CREATE TABLEとINSERTに直した方が動作確認できるので、適切なコメントが付き易いです。

[Orlofsky]

TABLEは 予約語 ですからテーブル名には使えません。
Oracle19C SQL 予約語 https://docs.oracle.com/cd/F19136_01/sqlrf/Oracle-SQL-Reserved-Words.html#GUID-55C49D1E-BE08-4C50-A9DD-8593EB925612

[nemui_00]

ご指摘・アドバイスありがとうございます！
指摘いただいた内容で質問を修正しました。

[sazi]

希望する結果を追記して下さい。

[nemui_00]

ご指摘ありがとうございます。希望する結果を追記しました。

Answer 1

階層を辿る必要があるので、階層問合せではできません。
再帰問合せを用います。
津島博士のパフォーマンス講座　第31回 再帰的問合せについて

手打ちで検証もしていませんが、イメージは以下の様な感じだと思います

with tree(ID, ID_PARENT, VL_ITEM, RT_PARENT, lvl, clc_VL_ITEM) as (
  select ID, ID_PARENT, VL_ITEM, RT_PARENT
       , 1, VL_ITEM
  from  TEST
  where ID='ID1'
union all
  select t.ID, t.ID_PARENT, t.VL_ITEM, t.RT_PARENT
       , tr.lvl+1, tr.clc_VL_ITEM * t.VL_ITEM
  from  TEST t inner join tree tr
        on  t.ID_PARENT=tr.ID
)
select * from tree

Comments

[nemui_00]

回答いただいたSQLを参考にしたところ、うまくいきました。
大変勉強になりました。ありがとうございます。

Answer 2

見ていてわかりにくいのでSQLをちょっと編集しました。

計算後数量 ってわからないので憶測で、質問に希望されるSELECT結果もあると良いですが、インラインビューを使えば良いってことでしょうか？

テーブル名、列名や別名に(今回の質問のように)1文字でもマルチバイト文字を使いたいときはダブルクォートで囲んでください。

SQL> SELECT DISTINCT
  2      LEVEL
  3    , ID
  4    , ID_PARENT
  5    , RT_PARENT AS "親に対する必要数"
  6    , VL_ITEM AS "数量"
  7    , PRIOR CALC_ITEM AS "計算後数量"
  8  FROM(
  9      SELECT
 10          ID
 11        , ID_PARENT
 12        , RT_PARENT
 13        , VL_ITEM
 14        , VL_ITEM * RT_PARENT AS CALC_ITEM
 15      FROM TEST
 16      )
 17  START WITH ID = 'ID1'
 18  CONNECT BY PRIOR ID = ID_PARENT
 19  ORDER BY
 20      LEVEL
 21    , ID
 22    , ID_PARENT;

     LEVEL ID         ID_PARENT  親に対する必要数       数量 計算後数量
---------- ---------- ---------- ---------------- ---------- ----------
         1 ID1                                             2
         2 ID2        ID1                       2          4
         3 ID3        ID2                       3         12          8
         3 ID4        ID2                       1          4          8

SQL>

Comments

[nemui_00]

ご回答・アドバイスありがとうございます。説明不足で重ね重ね申し訳ありません。
更新後テーブルから求めたい結果を追加しましたので、ご確認いただけますと幸いです。