Recursive multiplication How can I get ASSY_EXT_QTY to be the quantity of all the path?
IE:
LVL   QTY   ASSY_EXT_QTY
1     1     1
2     3     3 1*3
3     2     6 1*3*2
...

I thought making a path but for the quantities and then somehow multiply them but this seems far reached. This is the code:
select
  LEVEL,
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '&gt;') ROUTE,
  CASE
    LEVEL
    WHEN 1 THEN COMPONENT_QTY
    ELSE (PRIOR COMPONENT_QTY) * COMPONENT_QTY &lt;--- 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

I'd be just SQL, no functions or something like that as I'm not allowed by the environment.
Thanks!
ODCI
Personally, I'd probably write my own aggregate function so that the SQL looks like
select
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '&gt;') ROUTE,
  my_mult_agg( ) over (order by level) ASSY_EXT_QTY
from ( .. ) bom

(There still might be a pure SQL way)
Example:
Modified from the example in the documentation found here
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 &lt;= 10
order by level;

result: