Accessing ancestors in Oracle hierarchical query (CONNECT BY PRIOR)

Question

I'm currently working on building a Bill of materials for a few projects and have run into an issue.

I have a source table with the following columns:

• Part
• Component
• qty

Naturally there are other columns, but for the sake of simplicity...

The component can of course also be an assembly as well.

The following query will yield my hierarical view of a part and its sub components

SELECT
    Part,
    Component,
    qty

FROM
    sourceTable

CONNECT BY
    PRIOR Component = Part

START WITH
    Part = '<Part number here>'

There is one caveat though. The source table also contains items that are not consumed, (alternative items etc.) in this case the qty column is set to 0

All subsequent parts are not however set to 0.

I can get around this on a sub-structure by accessing the PRIOR element qty thusly.

SELECT
    Part,
    Component,
    qty,
    PRIOR qty * qty AS QTY_PER_MAIN_ASSEMBLY

FROM
    sourceTable

CONNECT BY
    PRIOR Component = Part

START WITH
    Part = '<Part number here>'

How would I go about this on a sub-sub-assembly or lower levels in the hierarchy?

I'd basically like to know how to access n-level PRIOR qty in this case

--------------------------- EDIT ---------------------------

Figured it out with the help of some googling

By using the SYS_CONNECT_BY_PATH on the qty column, I was able to build a string containing the qty hierarchy

following this i had to build a function that takes this string and splits it into individual values, and multiplies these

CREATE OR REPLACE FUNCTION eval (expr_in IN VARCHAR2)
  RETURN NUMBER
  AUTHID CURRENT_USER
  DETERMINISTIC
  RESULT_CACHE
IS
  PRAGMA UDF;
  v_res NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || expr_in || ' FROM DUAL' INTO v_res;
  RETURN v_res;
END eval;

Using above function, I just had to insert this into my select query

EVAL(SUBSTR( SYS_CONNECT_BY_PATH( TO_CHAR(qty), '*' ) , 2)) AS QTY_PER_MAIN_ASSEMBLY

Answer 1

You found a solution, but there is an alternative, without the need of function, recursive query:

with t(part, component, qty, qty_per_main_assembly) as (
    select part, component, qty, qty 
      from sourcetable 
      where part = '<Part number here>'
    union all
    select s.part, s.component, s.qty, t.qty_per_main_assembly * s.qty
      from t join sourcetable s on t.component = s.part)
select * from t

^{dbfiddle example}