如何讓 ASSY_EXT_QTY 成為所有路徑的數量？IE：

LVL   QTY   ASSY_EXT_QTY
1     1     1
2     3     3 1*3
3     2     6 1*3*2
...

我想為數量開闢一條道路，然後以某種方式將它們相乘，但這似乎遙不可及。這是程式碼：

select
 LEVEL,
 SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
 CASE
   LEVEL
   WHEN 1 THEN COMPONENT_QTY
   ELSE (PRIOR COMPONENT_QTY) * COMPONENT_QTY <--- RIGHT NOW I'M DOING THIS BUT NOT ENOUGH AS WORKS IN THE CURRENT SUB-LEVEL ONLY.
 END ASSY_EXT_QTY
from
 (
   select
...
   FROM
...
   WHERE
...
 ) START WITH FATHER_ITEM_NUMBER = :ASSY CONNECT BY PRIOR CHILD_ITEM_ID = STR_ITEM_ID
) bom

我只是 SQL，沒有函式或類似的東西，因為環境不允許。

謝謝！

ODCI

就個人而言，我可能會編寫自己的聚合函式，以便 SQL 看起來像

select
 SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
 my_mult_agg( ) over (order by level) ASSY_EXT_QTY
from ( .. ) bom

（可能還有純 SQL 方式）

例子：

從此處找到的文件中的範例修改

create or replace
type my_mult_agg_impl as object
(
 mult_buffer NUMBER, -- highest value seen so far 
 static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl) 
   return number,
 member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, 
   value IN number) return number,
 member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, 
   value IN number) return number,
 member function ODCIAggregateTerminate(self IN my_mult_agg_impl, 
   returnValue OUT number, flags IN number) return number,
 member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, 
   ctx2 IN my_mult_agg_impl) return number
);
/

create or replace type body my_mult_agg_impl is 
static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl) 
return number is 
begin
 sctx := my_mult_agg_impl(1);
 return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
 mult_buffer := mult_buffer * value;
 
 return ODCIConst.Success;
end;
member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
 mult_buffer := mult_buffer / value;
 
 return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN my_mult_agg_impl, 
   returnValue OUT number, flags IN number) return number is
begin
 returnValue := mult_buffer;
 return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, ctx2 IN my_mult_agg_impl) return number is
begin
 self.mult_buffer := self.mult_buffer * ctx2.mult_buffer;
 
 return ODCIConst.Success;
end;
end;
/

CREATE or replace FUNCTION my_mult_agg (input NUMBER) RETURN NUMBER 
AGGREGATE USING my_mult_agg_impl;
/

select level,
my_mult_agg( level ) over (order by level) as MM
from dual
connect by level <= 10
order by level;

結果：

這是將行相乘而不是求和的老技巧：

drop table t1 purge;
create table t1 (lvl number, qty number);
insert into t1 values (1, 1);
insert into t1 values (2, 3);
insert into t1 values (3, 4);
insert into t1 values (4, 2);
insert into t1 values (5, 3);
commit;
select * from t1;

      LVL        QTY
---------- ----------
        1          1
        2          3
        3          4
        4          2
        5          3

select
lvl, qty,
round(exp(sum(ln(qty)) over (order by lvl))) as assy_ext_qty
from
 t1;

      LVL        QTY ASSY_EXT_QTY
---------- ---------- ------------
        1          1            1
        2          3            3
        3          4           12
        4          2           24
        5          3           72

當心分數和四捨五入。

引用自：https://dba.stackexchange.com/questions/275260