11 Replies Latest reply: Jul 10, 2013 5:19 AM by BluShadow RSS

    Formula Value Creation

    931711

      Hi All,

       

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

       

      STEPOPERATORVARIABLEVAR_VALUE
      1A10
      20B20
      3*
      4(C30
      5/D40
      6)
      70E50
      8-0
      9(F60
      10*G70
      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
          Hoek

          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
            931711

            Dear Hoek

             

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

             

            Regards,

            BS.

            • 3. Re: Formula Value Creation
              BluShadow

              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
                Purvesh K

                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

                  The answer I gave you on last weeks thread, with your new formula and values...

                   

                  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
                  23        start with 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
                    BluShadow

                    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

                      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_

                        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 )     -4125

                         

                        SQL>

                        • 9. Re: Formula Value Creation
                          Hoek

                          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
                            BluShadow

                            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.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
                            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

                              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*