
1. Re: Formula Value Creation
Hoek Jul 10, 2013 4:00 AM (in response to 931711)If you have Oracle OLAP (currently not available for me ) installed, then check out DBMS_AW.EVAL_NUMBER:

2. Re: Formula Value Creation
931711 Jul 10, 2013 4:04 AM (in response to Hoek)Dear Hoek
Unfortunately , I don't have olap installed. I want it through sql statements.
Regards,
BS.

3. Re: Formula Value Creation
BluShadow Jul 10, 2013 4:10 AM (in response to 931711)And what's wrong with the answers you had a week ago when you asked the same question?

4. Re: Formula Value Creation
Purvesh K Jul 10, 2013 4:14 AM (in response to 931711)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 start with rn = 1 ); 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
BluShadow Jul 10, 2013 4:16 AM (in response to BluShadow)The answer I gave you on last weeks thread, with your new formula and values...
SQL> ed
Wrote file afiedt.buf1 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
23 start with line_no = 1
24 ) x
25* ,xmltable(x.formula) y
SQL> /FORMULA RESULT
 
10+20*(30 div 40)+50(60*70) 4125Works for me.

6. Re: Formula Value Creation
BluShadow Jul 10, 2013 4:27 AM (in response to Purvesh K)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
931711 Jul 10, 2013 4:36 AM (in response to BluShadow)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.
Thanks for your help :)

8. Re: Formula Value Creation
_Karthick_ Jul 10, 2013 4:54 AM (in response to 931711)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
34 start with step = 1
35 connect by step = prior step + 1;FORMULA VAL
 
10 + 20 * ( 30 / 40 ) + 50  ( 60 * 70 ) 4125SQL>

9. Re: Formula Value Creation
Hoek Jul 10, 2013 4:55 AM (in response to 931711)That's exactly what Blu has showed you *twice*. I'm thinking you don't understand/are a bit confused by the WITHclause 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 copypaste 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
BluShadow Jul 10, 2013 4:56 AM (in response to 931711)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.
Thanks for your help :)
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.buf1 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(operatorvariable
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
26 start with 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
BluShadow Jul 10, 2013 5:19 AM (in response to Hoek)Hoek wrote:
That's exactly what Blu has showed you *twice*. I'm thinking you don't understand/are a bit confused by the WITHclause Blu has used.
Which was explained to him on last weeks thread. *sigh*