# Formula Value Creation

Hi All,

I want to create a final value based on the formula created from the table:

 STEP OPERATOR VARIABLE VAR_VALUE 1 A 10 2 0 B 20 3 * 4 ( C 30 5 / D 40 6 ) 7 0 E 50 8 -0 9 ( F 60 10 * G 70 11 )

The Formula that i am creating is

SELECT LISTAGG(operator || variable)

WITHIN GROUP(ORDER BY step) formula

FROM bharat_formula;

The result is A+B*(C/D)+E-(F*G)

and the formula with value that i am creating is :

SELECT LISTAGG(operator || var_value)

WITHIN GROUP(ORDER BY step) formula

FROM bharat_formula;

and the result is 10+20*(30/40)+50-(60*70)

Now i want to formulate the formula as 10+20*(30/40)+50-(60*70) and get the calculated value of the formula as -4125.

Can any body please help me in how to create the formula and calculate the value in a single statement?

• ###### 1. Re: Formula Value Creation

If you have Oracle OLAP (currently not available for me ) installed, then check out DBMS_AW.EVAL_NUMBER:

https://forums.oracle.com/message/9423580?#9423580

Summary of DBMS_AW Subprograms

• ###### 2. Re: Formula Value Creation

Dear Hoek

Unfortunately , I don't have olap installed. I want it through sql statements.

Regards,

BS.

• ###### 3. Re: Formula Value Creation

And what's wrong with the answers you had a week ago when you asked the same question?

Dynamic Formula Creation

• ###### 4. Re: Formula Value Creation

Not possible in a Single SQL since it is dynamic. But this seems best way to do the job:

```create or replace function evaluate_expr (p_exp in varchar2)
return number is
v_out number;
begin
execute immediate 'begin :val := ' || p_exp || '; end;' using out v_out;
return v_out;
exception
when others then
v_out := -9999;
end;

with data as
(
select null op, 'A' var, 10 val from dual union all
select '+' op, 'B' var, 20 val from dual union all
select '*' op, null var, null val from dual union all
select '(' op, 'C' var, 30 val from dual union all
select '/' op, 'D' var, 40 val from dual union all
select ')' op, null var, null val from dual union all
select '+' op, 'E' var, 50 val from dual union all
select '-' op, null var, null val from dual union all
select '(' op, 'F' var, 60 val from dual union all
select '*' op, 'G' var, 70 val from dual union all
select ')' op, null var, null val from dual
)
select exp, evaluate_expr(val_exp) value
from (
select replace(ltrim(max(sys_connect_by_path(exp, '~')) keep (dense_rank last order by rn), '~'), '~', ' ') exp,
replace(ltrim(max(sys_connect_by_path(val_exp, '~')) keep (dense_rank last order by rn), '~'), '~', ' ') val_exp
from (
select 'txt' col, op || ' ' || var exp,
op || ' ' || val val_exp,
row_number() over (order by rownum) rn
from data
)
group by col
connect by rn - 1 = prior rn
and prior col = col
);

EXP                                    VALUE
-------------------------------------- ----------------------
A + B *  ( C / D )  + E -  ( F * G )  -4125

```

Since, I do not have 11g at hand, I have used different string aggregation technique. To test on 11g, remove the code for Connect By and and SYS_CONNECT_BY_PATH with your LISTAGG function. Also, would suggest you to peform correct exception handling to understand the reason of failure, instead of what I have demonstrated in my example. Handling of Exception must be followed by a RAISE to propogate the exception.

• ###### 5. Re: Formula Value Creation

SQL> ed
Wrote file afiedt.buf

1  with t as (select 1 as line_no, cast(null as varchar2(1)) as operator, 'A' as variable from dual union all
2             select 2, '+', 'B' from dual union all
3             select 3, '*', '' from dual union all
4             select 4, '(', 'C' from dual union all
5             select 5, '/', 'D' from dual union all
6             select 6, ')', '' from dual union all
7             select 7, '+', 'E' from dual union all
8             select 8, '-', '' from dual union all
9             select 9, '(', 'F' from dual union all
10             select 10, '*', 'G' from dual union all
11             select 11, ')', '' from dual)
12      ,v as (select 10 as a, 20 as b, 30 as c, 40 as d, 50 as e, 60 as f, 70 as g from dual)
13  --
14  select x.formula
15        ,y.column_value as result
16  from (
17        select replace(sys_connect_by_path(replace(operator,'/',' div ')||
18                                           decode(variable,'A',a,'B',b,'C',c,'D',d,'E',e,'F',f,'G',g)
19                                          ,'~'),'~') as formula
20        from   t cross join v
21        where connect_by_isleaf = 1
22        connect by line_no = prior line_no + 1
24       ) x
25*     ,xmltable(x.formula) y
SQL> /

FORMULA                                  RESULT
---------------------------------------- --------------------
10+20*(30 div 40)+50-(60*70)             -4125

Works for me.

• ###### 6. Re: Formula Value Creation

PurveshK wrote:

select replace(ltrim(max(sys_connect_by_path(exp, '~')) keep (dense_rank last order by rn), '~'), '~', ' ') exp,

replace(ltrim(max(sys_connect_by_path(val_exp, '~')) keep (dense_rank last order by rn), '~'), '~', ' ') val_exp

Ouch.  No need for all that MAX and KEEP stuff.  Just use CONNECT_BY_ISLEAF = 1 in the where clause.

• ###### 7. Re: Formula Value Creation

Dear Blu,

I got how to create the formula , i can get the value in another step , but what i want is i want to create the formula and its value in a single step.

• ###### 8. Re: Formula Value Creation

May be

SQL> with t
2  as
3  (
4  select 01 step, ''  operator, 'A' variable, 10    var_value from dual union all
5  select 02 step, '0' operator, 'B' variable, 20    var_value from dual union all
6  select 03 step, '*' operator, ''  variable, null  var_value from dual union all
7  select 04 step, '(' operator, 'C' variable, 30    var_value from dual union all
8  select 05 step, '/' operator, 'D' variable, 40    var_value from dual union all
9  select 06 step, ')' operator, ''  variable, null  var_value from dual union all
10  select 07 step, '0' operator, 'E' variable, 50    var_value from dual union all
11  select 08 step, '-0'operator, ''  variable, null  var_value from dual union all
12  select 09 step, '(' operator, 'F' variable, 60    var_value from dual union all
13  select 10 step, '*' operator, 'G' variable, 70    var_value from dual union all
14  select 11 step, ')' operator, ''  variable, null  var_value from dual
15  )
16  select replace(sys_connect_by_path(str, ','), ',') formula
17       , xmltype
18         (
19            dbms_xmlgen.getxml
20            (
21               'select ' || replace(sys_connect_by_path(str, ','), ',') ||
22               ' val from dual'
23            )
24         ).extract('/ROWSET/ROW/VAL/text()') val
25    from (
26            select step
27                 , ' '
28                   || decode(operator, null, ' ', '0', '+', '-0', '-', operator)
29                   || ' '
30                   || to_char(var_value) str
31              from t
32         )
33   where connect_by_isleaf = 1
35  connect by step = prior step + 1;

FORMULA                                            VAL
-------------------------------------------------- ----------
10 + 20 *  ( 30 / 40 )  + 50 -  ( 60 * 70 )     -4125

SQL>

• ###### 9. Re: Formula Value Creation

That's exactly what Blu has showed you *twice*. I'm thinking you don't understand/are a bit confused by the WITH-clause Blu has used.

T and V = YOUR TABLES, just leave out the WITH CLAUSE from the examples and change T and V into YOUR TABLES.

If you post CREATE TABLE + INSERT INTO statements, you'll even get a copy-paste answer, ready for production.

You can read more here  (see #7 Sample Data) : https://forums.oracle.com/message/9362002#9362002

• ###### 10. Re: Formula Value Creation

931711 wrote:

Dear Blu,

I got how to create the formula , i can get the value in another step , but what i want is i want to create the formula and its value in a single step.

Which is what you were shown last week and I've demonstrated again today.

What's the problem with the solutions provided?  They are doing just what you asked for.... see... a single SQL statement...

SQL> ed
Wrote file afiedt.buf

1  with t as (select 1 as line_no, cast(null as varchar2(1)) as operator, 'A' as variable from dual union all
2             select 2, '+', 'B' from dual union all
3             select 3, '*', '' from dual union all
4             select 4, '(', 'C' from dual union all
5             select 5, '/', 'D' from dual union all
6             select 6, ')', '' from dual union all
7             select 7, '+', 'E' from dual union all
8             select 8, '-', '' from dual union all
9             select 9, '(', 'F' from dual union all
10             select 10, '*', 'G' from dual union all
11             select 11, ')', '' from dual)
12      ,v as (select 10 as a, 20 as b, 30 as c, 40 as d, 50 as e, 60 as f, 70 as g from dual)
13  --
14  select x.formula1 as formula
15        ,x.formula2 as reduced_formula
16        ,y.column_value as result
17  from (
18        select replace(sys_connect_by_path(operator||variable
19                                          ,'~'),'~') as formula1
20              ,replace(sys_connect_by_path(operator||
21                                           decode(variable,'A',a,'B',b,'C',c,'D',d,'E',e,'F',f,'G',g)
22                                          ,'~'),'~') as formula2
23        from   t cross join v
24        where connect_by_isleaf = 1
25        connect by line_no = prior line_no + 1
27       ) x
28*     ,xmltable(replace(x.formula2,'/',' div ')) y
SQL> /

FORMULA                                  REDUCED_FORMULA                          RESULT
---------------------------------------- ---------------------------------------- --------------------
A+B*(C/D)+E-(F*G)                        10+20*(30/40)+50-(60*70)                 -4125

... providing both the formula and the result.

• ###### 11. Re: Formula Value Creation

Hoek wrote:

That's exactly what Blu has showed you *twice*. I'm thinking you don't understand/are a bit confused by the WITH-clause Blu has used.

Which was explained to him on last weeks thread.  *sigh*