Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create table Calc(
  CALC_CD  VARCHAR2(10),
  FORMULA1 VARCHAR2(200),
  FORMULA2 VARCHAR2(200),
  FORMULA3 VARCHAR2(200),
  FORMULA4 VARCHAR2(200)
);
Create table UnitData(
  UnitNumber VARCHAR2(100),
  CALC_CD    VARCHAR2(10),
  GRP_CD     VARCHAR2(100),
  GRP_AMT    NUMBER
);
insert into Calc (calc_cd, formula1, formula2, formula3, formula4)
  SELECT 'M10', 'SI01/VI01', 'SI02/VI02', 'Part1 * 0.3', 'Part2 + Part3' FROM DUAL UNION ALL
  SELECT 'M10', 'SI01', 'VI02', 'Part1', 'Part3 ^ Part2' FROM DUAL UNION ALL
  SELECT 'M10', 'SI01 - VI01', 'SI02 + VI02', 'Part1 * 0.3 + 2', 'Part2 / Part3' FROM DUAL;
3 rows affected

insert into UnitData (unitnumber, calc_cd, grp_cd, grp_amt)
  select '111E', 'M10', 'SI01', 233.23 from dual union
  select '111E', 'M10', 'VI01',   4.98 from dual union
  select '111E', 'M10', 'SI02', 233.23 from dual union
  select '111E', 'M10', 'VI02',   4.98 from dual union
  select '112E', 'M10', 'SI01', 133.23 from dual union
  select '112E', 'M10', 'VI01',   3.98 from dual union
  select '112E', 'M10', 'SI02', 133.23 from dual union
  select '112E', 'M10', 'VI02',   3.98 from dual
8 rows affected

COMMIT;
Select * From Calc


CALC_CD
FORMULA1
FORMULA2
FORMULA3
FORMULA4


M10
SI01/VI01
SI02/VI02
Part1 * 0.3
Part2 + Part3

M10
SI01
VI02
Part1
Part3 ^ Part2

M10
SI01 - VI01
SI02 + VI02
Part1 * 0.3 + 2
Part2 / Part3


Select * From UnitData


UNITNUMBER
CALC_CD
GRP_CD
GRP_AMT


111E
M10
SI01
233.23

111E
M10
VI01
4.98

111E
M10
SI02
233.23

111E
M10
VI02
4.98

112E
M10
SI01
133.23

112E
M10
VI01
3.98

112E
M10
SI02
133.23

112E
M10
VI02
3.98


WITH FUNCTION is_operator(term VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
    RETURN CASE
           WHEN term IS NULL THEN NULL
           WHEN term IN ('+ ', '- ', '/ ', '* ', '^ ') THEN 1
           ELSE 0
           END;
  END;
FUNCTION priority(operator VARCHAR2)
  RETURN NUMBER
  IS
  BEGIN
    RETURN CASE operator
           WHEN '+ ' THEN 1
           WHEN '- ' THEN 1
           WHEN '/ ' THEN 2
           WHEN '* ' THEN 2
           WHEN '^ ' THEN 3
           END;
  END;
calc1 (calc_cd, formula2, formula3, formula4) AS (
  SELECT calc_cd,
         REPLACE(formula2, 'Part1', '('||formula1||')'),
         REPLACE(formula3, 'Part1', '('||formula1||')'),
         REPLACE(formula4, 'Part1', '('||formula1||')')
  FROM   calc
),
calc2 (calc_cd, formula3, formula4) AS (
  SELECT calc_cd,
         REPLACE(formula3, 'Part2', '('||formula2||')'),
         REPLACE(formula4, 'Part2', '('||formula2||')')
  FROM   calc1
),
calc3 (calc_cd, formula) AS (
  SELECT calc_cd,
         REPLACE(formula4, 'Part3', '('||formula3||')')
  FROM   calc2
),
shunting_yard (calc_cd, formula, input, stack, output) AS (
  SELECT calc_cd,
         formula,
         TRIM(
           REPLACE(
             REGEXP_REPLACE(
               formula,
               '\s*([()^*/+-])\s*',
               ' \1 '
             ),
             '  ',
             ' '
           )
         ) || ' ',
         CAST(NULL AS VARCHAR2(4000)),
         CAST(NULL AS VARCHAR2(4000))
  FROM   calc3
UNION ALL
  SELECT calc_cd,
         formula,
         CASE
         WHEN (f_term = ') ' AND s_term != '( ')
         THEN input
         WHEN f_term IN ('( ', ') ')
         OR   is_operator(f_term) = 0
         OR   stack IS NULL
         OR   s_term = '( '
         OR   priority(s_term) < priority(f_term)
         THEN SUBSTR(input, INSTR(input, ' ') + 1)
         ELSE input
         END,
         CASE
         WHEN f_term = '( '
         THEN f_term || stack
         WHEN f_term = ') '
         THEN SUBSTR(stack, INSTR(stack, ' ') + 1)
         WHEN is_operator(f_term) = 0
         THEN stack
         WHEN stack IS NULL
         OR   s_term = '( '
         OR   priority(s_term) < priority(f_term)
         THEN f_term || stack
         ELSE SUBSTR(stack, INSTR(stack, ' ') + 1)
         END,
         CASE
         WHEN f_term = '( '
         OR   (f_term = ') ' AND s_term = '( ')
         THEN output
         WHEN f_term = ') ' AND s_term != '( '
         THEN output || s_term
         WHEN is_operator(f_term) = 0
         THEN output || f_term
         WHEN stack IS NULL
         OR   s_term = '( '
         OR   priority(s_term) < priority(f_term)
         THEN output
         ELSE output || s_term
         END
  FROM   shunting_yard
         CROSS JOIN LATERAL (
           SELECT SUBSTR(input, 1, INSTR(input, ' '))
                    AS f_term,
                  SUBSTR(stack, 1, INSTR(stack, ' '))
                    AS s_term
           FROM   DUAL
         )
)
SEARCH DEPTH FIRST BY calc_cd, input SET order_id
CYCLE calc_cd, input, stack, output SET done TO 1 DEFAULT 0,
reverse_polish (unitnumber, calc_cd, formula, input, stack) AS (
  SELECT CAST(NULL AS VARCHAR2(100)),
         calc_cd,
         formula,
         output,
         CAST(NULL AS VARCHAR2(4000))
  FROM   shunting_yard
  WHERE  done = 1
UNION ALL
  SELECT t.unitnumber,
         r.calc_cd,
         r.formula,
         SUBSTR(r.input, INSTR(r.input, ' ', 1, 1) + 1),
         CASE t.f_term
         WHEN '+'
         THEN TO_CHAR(t.s_term2 + t.s_term1) || ' ' || t.s_tail
         WHEN '-'
         THEN TO_CHAR(t.s_term2 - t.s_term1) || ' ' || t.s_tail
         WHEN '*'
         THEN TO_CHAR(t.s_term2 * t.s_term1) || ' ' || t.s_tail
         WHEN '/'
         THEN TO_CHAR(t.s_term2 / t.s_term1) || ' ' || t.s_tail
         WHEN '^'
         THEN TO_CHAR(POWER(t.s_term2, t.s_term1)) || ' ' || t.s_tail
         ELSE COALESCE(TO_CHAR(t.value), t.f_term) || ' ' || r.stack
         END
  FROM   reverse_polish r
         CROSS JOIN LATERAL (
           SELECT SUBSTR(r.input, 1, INSTR(r.input, ' ') - 1) AS f_term,
                  TO_NUMBER(SUBSTR(r.stack, 1, INSTR(r.stack, ' ') - 1)) AS s_term1,
                  TO_NUMBER(
                    CASE 
                    WHEN INSTR(r.stack, ' ', 1, 2) > 0
                    THEN TO_NUMBER(
                           SUBSTR(
                             r.stack,
                             INSTR(r.stack, ' ', 1, 1) + 1,
                             INSTR(r.stack, ' ', 1, 2) - INSTR(r.stack, ' ', 1, 1) - 1
                           )
                         )
                    END
                  ) AS s_term2,
                  SUBSTR(r.stack, INSTR(r.stack, ' ', 1, 2) + 1) AS s_tail,
                  COALESCE(u.unitnumber, r.unitnumber) AS unitnumber,
                  u.grp_amt AS value
           FROM   DUAL
                  LEFT OUTER JOIN unitdata u
                  ON     (r.unitnumber IS NULL OR r.unitnumber = u.unitnumber)
                     AND r.calc_cd = u.calc_cd
                     AND SUBSTR(r.input, 1, INSTR(r.input, ' ') - 1) = u.grp_cd
         ) t
  WHERE  r.input IS NOT NULL
)
SEARCH DEPTH FIRST BY unitnumber, calc_cd, input SET order_id
CYCLE unitnumber, calc_cd, input, stack SET done TO 1 DEFAULT 0
SELECT unitnumber,
       calc_cd,
       formula,
       TO_NUMBER(stack) AS value
FROM   reverse_polish
WHERE  input IS NULL;


UNITNUMBER
CALC_CD
FORMULA
VALUE


111E
M10
((SI01)) ^ (VI02)
618822894936.360378888509577862392629892

112E
M10
((SI01)) ^ (VI02)
285703835.699108676302209925319691931182

111E
M10
(SI02 + VI02) / ((SI01 - VI01) * 0.3 + 2)
3.38006385242993969492727917701312522171

112E
M10
(SI02 + VI02) / ((SI01 - VI01) * 0.3 + 2)
3.36505211526670754138565297363580625383

111E
M10
(SI02/VI02) + ((SI01/VI01) * 0.3)
60.8833333333333333333333333333333333333

112E
M10
(SI02/VI02) + ((SI01/VI01) * 0.3)
43.5173366834170854271356783919597989949

CALC_CD	FORMULA1	FORMULA2	FORMULA3	FORMULA4
M10	SI01/VI01	SI02/VI02	Part1 * 0.3	Part2 + Part3
M10	SI01	VI02	Part1	Part3 ^ Part2
M10	SI01 - VI01	SI02 + VI02	Part1 * 0.3 + 2	Part2 / Part3

UNITNUMBER	CALC_CD	GRP_CD	GRP_AMT
111E	M10	SI01	233.23
111E	M10	VI01	4.98
111E	M10	SI02	233.23
111E	M10	VI02	4.98
112E	M10	SI01	133.23
112E	M10	VI01	3.98
112E	M10	SI02	133.23
112E	M10	VI02	3.98

UNITNUMBER	CALC_CD	FORMULA	VALUE
111E	M10	((SI01)) ^ (VI02)	618822894936.360378888509577862392629892
112E	M10	((SI01)) ^ (VI02)	285703835.699108676302209925319691931182
111E	M10	(SI02 + VI02) / ((SI01 - VI01) * 0.3 + 2)	3.38006385242993969492727917701312522171
112E	M10	(SI02 + VI02) / ((SI01 - VI01) * 0.3 + 2)	3.36505211526670754138565297363580625383
111E	M10	(SI02/VI02) + ((SI01/VI01) * 0.3)	60.8833333333333333333333333333333333333
112E	M10	(SI02/VI02) + ((SI01/VI01) * 0.3)	43.5173366834170854271356783919597989949