11 Replies Latest reply: Aug 19, 2013 2:50 PM by jihuyao RSS

    Calculation

    931711

      Hi Gurus,

       

      I have a requirement as below:

       

      I have two tables as :

       

      TABLE_ONE

      -------------------------

      IDOperatorVariableValue
      1(A10
      2/B20
      3)
      4*C50
      5#
      6+
      7(D50
      8/E100
      9)
      10*F70
      11#
      12+
      13(G80
      14/H40
      15)
      16*I0.1
      17*J100

       

       

      and another table is

       

      TABLE_TWO

      ---------------------------

      IDFACTOR_1FACTOR_2TOTAL
      40.55025
      100.57035
      170.210020

       

      ----------------------------------------------------------------------------------------------------------

      The question is:

       

      There will be two factors involved: factor_1 and factor_2  as shown in the second table.

       

      in the first table, there are several components separated by '#'.

       

      The value of the variable just above the # will be inserted into the factor_2 column in the second table.

      The factor_1 column will be the calculation of rest  of the variables (Excluding the variable just above the '#').

       

      E.G:

       

      For the sample below:

       

      13(G80
      14/H40
      15)
      16*I0.1
      17*J100

       

       

      The ID will be the Last variable's id (Just above the #)

      The FACTOR_2 will be 100 : Last variable's amount (Just above the #)

      The Factor_1 will be the calculation of rest of the fields.

      i.e (G/H)*I         =>       (80/40)*0.1 = 0.2

      and Total would be factor_1 * factor_2.

       

      So The table will look like

       

      IDFACTOR_1FACTOR_2TOTAL
      170.210020

       

       

      Please help me how will i achieve this??

       

      Thanks & Regards,

      BS.

        • 1. Re: Calculation
          Etbin

          Maybe (somehow by brute force)

           

          with

          table_one as

          (select 1 id,'(' operator,'A' variable,10 Value from dual union all

          select 2,'/','B',20 from dual union all

          select 3,')',null,null from dual union all

          select 4,'*','C',50 from dual union all

          select 5,'#',null,null from dual union all

          select 6,'+',null,null from dual union all

          select 7,'(','D',50 from dual union all

          select 8,'/','E',100 from dual union all

          select 9,')',null,null from dual union all

          select 10,'*','F',70 from dual union all

          select 11,'#',null,null from dual union all

          select 12,'+',null,null from dual union all

          select 13,'(','G',80 from dual union all

          select 14,'/','H',40 from dual union all

          select 15,')',null,null from dual union all

          select 16,'*','I',0.1 from dual union all

          select 17,'*','J',100 from dual

          )

          select id,

                 factor_1,

                 factor_2,

                 factor_1 * factor_2 total

            from (select id,

                         case when operator = '*' and lead(operator) over (partition by cnt order by id) = '*'

                              then '?'

                              else operator

                         end operator,

                         value,cnt,factor_2,

                         case when operator = '*' and lag(operator) over (partition by cnt order by id) = '*'

                              then factor_1 * lag(factor_2) over (partition by cnt order by id)

                              else factor_1

                         end factor_1

                    from (select id,operator,value,cnt,

                                 case when factor_1 is null

                                      then first_value(factor_1 ignore nulls) over (partition by cnt order by id)

                                      else factor_1 end factor_1,

                                 case when factor_2 is null

                                           then first_value(factor_2 ignore nulls) over (partition by cnt order by id)

                                           else factor_2 end factor_2

                            from (select id,operator,value,

                                         case when operator = '/'

                                              then lag(value) over (order by id) / value

                                         end factor_1,

                                         case when operator = '*' then

                                              value

                                         end factor_2,

                                         count(case when operator = '#' then 1 end) over (order by id) cnt

                                    from table_one

                                 )

                         )

                 )

          where operator = '*'

          order by id

           

          IDFACTOR_1FACTOR_2TOTAL
          4.55025
          10.57035
          17.210020

           

          Regards

           

          Etbin

          • 2. Re: Calculation
            gaverill

            Here's my stab at it...

             

            First I need a stored function to evaluate the constructed expression:

             

            create or replace function toNumber(aExpression in varchar2) return number deterministic
            is
                result    number;
            begin
                execute immediate '
                        begin
                                :result := ' || aExpression || ';
                            end;'
                    using    out    result;
                   
                    return    result;
            end;
            /
            
            


            with    t
            as    (
                    select 1 ID,    '(' Operator,     'A' Variable,     10 Value    from DUAL union all   
                    select 2,    '/',        'B',        20        from DUAL union all
                    select 3,    ')',        '',        null        from DUAL union all
                    select 4,    '*',        'C',        50        from DUAL union all
                    select 5,    '#',        '',        null        from DUAL union all
                    select 6,    '+',        '',        null        from DUAL union all
                    select 7,    '(',        'D',        50        from DUAL union all
                    select 8,    '/',        'E',        100        from DUAL union all
                    select 9,    ')',        '',        null        from DUAL union all
                    select 10,    '*',        'F',        70        from DUAL union all
                    select 11,    '#',        '',        null        from DUAL union all
                    select 12,    '+',        '',        null        from DUAL union all
                    select 13,    '(',        'G',        80        from DUAL union all
                    select 14,    '/',        'H',        40        from DUAL union all
                    select 15,    ')',        '',        null        from DUAL union all
                    select 16,    '*',        'I',        0.1        from DUAL union all
                    select 17,    '*',        'J',        100        from DUAL union all
                    select 18,    '#',        '',        null        from DUAL
                )
            ,    f2
            as    (
                select    f2.Id, f2.Operator, f2.Value
                    from    t
                    ,    t f2
                    where    t.Operator = '#'
                    and    f2.Id = t.Id - 1
                    )
            ,    f1
            as    (
                select    f2.Id F2_Id, listagg(f1.Operator || f1.Value, '') within group (order by f1.Id) F1_Expression
                    from    f2
                    ,    t f1
                    where    f1.Id between nvl((select max(x.Id) + 3 /* Skip the operator following #*/ from f2 x where x.Id < f2.Id), 1) and f2.Id - 1
                    group by
                        f2.Id
                    )
            select    f2.Id
            ,    f2.Value F2_Value
            ,    f1.F1_Expression
            ,    toNumber(f1.F1_Expression) F1_Value
            ,    toNumber(f1.F1_Expression || f2.Operator || to_char(f2.Value)) Total
            from    f2
            join    f1 on F2_Id = f2.Id;
            
            

             

            Which returns:

             

            IDF2_VALUEF1_EXPRESSIONF1_VALUETOTAL

            4

            50

            (10/20)

            0.5

            25

            10

            70

            (50/100)

            0.5

            35

            17

            100

            (80/40)*.1

            0.2

            20

             

            HTH

             

            Gerard

            • 3. Re: Calculation
              rp0428

              Sounds like you might be interested in Odie's PL/SQL RPN Calculator

              http://odieweblog.wordpress.com/2013/02/03/plsql-rpn-calculator/

              This is something I’ve developed recently to evaluate stored expressions (formulas) using variables.

              The “calculator” is written in PL/SQL and implements an RPN evaluation technique, as well as a method to convert infix expressions to RPN using the shunting-yard algorithm.

              The evaluation function does not involve any dynamic SQL. 

              Program and additional objects available here : rpn_util.zip

              • 4. Re: Calculation
                931711


                Hi Etbin,

                 

                Thank you for your help.

                 

                suppose i have the same table with some additional columns as below and my expected result ias as shown.

                 

                 

                18

                #

                 

                 

                19

                +

                X

                600

                20

                #

                 

                 

                21

                -

                Y

                1100

                 

                 

                And my result table would be

                 

                19

                1

                600

                600

                21

                1

                1100

                1100

                 

                Could you please help me in how to handle this?

                 

                Thanks & Regards,

                BS.

                • 5. Re: Calculation
                  Etbin

                  I think a calculation using an arbitrary formula cannot be handled with Analytic Functions.

                  It might be suitable for a group of particular cases when you don't want to use Dynamic SQL (Execute Immediate).

                   

                  with

                  table_one as

                  (select 1 id,'(' operator,'A' variable,10 Value from dual union all

                  select 2,'/','B',20 from dual union all

                  select 3,')',null,null from dual union all

                  select 4,'*','C',50 from dual union all

                  select 5,'#',null,null from dual union all

                  select 6,'+',null,null from dual union all

                  select 7,'(','D',50 from dual union all

                  select 8,'/','E',100 from dual union all

                  select 9,')',null,null from dual union all

                  select 10,'*','F',70 from dual union all

                  select 11,'#',null,null from dual union all

                  select 12,'+',null,null from dual union all

                  select 13,'(','G',80 from dual union all

                  select 14,'/','H',40 from dual union all

                  select 15,')',null,null from dual union all

                  select 16,'*','I',0.1 from dual union all

                  select 17,'*','J',100 from dual union all

                  select 18,'#',null,null from dual union all

                  select 19,'+','X',600 from dual union all

                  select 20,'#',null,null from dual union all

                  select 21,'-','Y',1100 from dual

                  )

                  select id,

                         factor_1,

                         factor_2,

                         factor_1 * factor_2 total

                    from (select id,

                                 case when operator = '*' and lead(operator) over (partition by cnt order by id) = '*'

                                      then '?'

                                      else operator

                                 end operator,

                                 value,cnt,factor_2,

                                 case when operator = '*' and lag(operator) over (partition by cnt order by id) = '*'

                                      then factor_1 * lag(factor_2) over (partition by cnt order by id)

                                      when operator in ('+','-')

                                      then to_number(operator || '1') /* must distinguish between the two signs */

                                      else factor_1

                                 end factor_1

                            from (select id,operator,value,cnt,

                                         case when factor_1 is null

                                              then first_value(factor_1 ignore nulls) over (partition by cnt order by id)

                                              else factor_1 end factor_1,

                                         case when factor_2 is null

                                                   then first_value(factor_2 ignore nulls) over (partition by cnt order by id)

                                                   else factor_2 end factor_2

                                    from (select id,operator,value,

                                                 case when operator = '/'

                                                      then lag(value) over (order by id) / value

                                                 end factor_1,

                                                 case when operator in ('+','-','*')

                                                      then value

                                                 end factor_2,

                                                 count(case when operator = '#' then 1 end) over (order by id) cnt

                                            from table_one

                                         )

                                 )

                         )

                  where operator in ('+','-','*')

                     and value is not null

                  order by id

                   

                  Regards

                   

                  Etbin

                  • 6. Re: Calculation
                    931711

                    Hi Etbin,

                     

                    I am confused again.

                     

                    Let me explain this again...

                     

                    IDOperatorVariableValue
                    1(

                    2(A10
                    3/B20
                    4)

                    5*C50
                    6*D10
                    7)

                    8#

                    9+

                    10(D50
                    11/E100
                    12)

                    13*F70
                    14#

                    15+

                    16(G80
                    17/H40
                    18)

                    19*I0.1
                    20*J100
                    21#

                    22+X600
                    23#

                    24-Y1100

                     

                     

                    The variable just before the '#' will always have the amount factor associated with it.

                     

                    and the result column is:

                     

                    IDFACTOR_1FACTOR_2TOTAL
                    515050
                    62510250
                    130.57035
                    1910.10.1
                    20210020
                    221600600
                    24111001100

                     

                    Hope the table is much clear now.

                     

                    I tried to put the statement in a procedure . But it disnt work for me

                     

                    Please provide your further support to get it done.

                     

                    Thanks & Regardsn,

                    BS.

                    • 7. Re: Calculation
                      Stew Ashton

                      [EDIT: changed dbms_aw.eval_number to xmltable after reading BluShadow's post.]

                      drop table t;

                      create table t(ID,Operator,Variable,Value) as select

                      1,'(','A',10 from dual union all select

                      2,'/','B',20 from dual union all select

                      3,')',null,null from dual union all select

                      4,'*','C',50 from dual union all select

                      5,'#',null,null from dual union all select

                      6,'+',null,null from dual union all select

                      7,'(','D',50 from dual union all select

                      8,'/','E',100 from dual union all select

                      9,')',null,null from dual union all select

                      10,'*','F',70 from dual union all select

                      11,'#',null,null from dual union all select

                      12,'+',null,null from dual union all select

                      13,'(','G',80 from dual union all select

                      14,'/','H',40 from dual union all select

                      15,')',null,null from dual union all select

                      16,'*','I',0.1 from dual union all select

                      17,'*','J',100 from dual;

                       

                      select id, to_number(column_value) factor1, factor2, factor2*to_number(column_value) total

                      from (

                        select id_last_val id,

                          listagg(

                            case when id < id_last_val then

                              operator||to_char(value, 'tm', 'nls_numeric_characters=''.,''')

                            end

                          ) within group(order by id) factor1,

                        last_val factor2

                        from (

                          select a.*,

                          first_value(value) over(partition by grp order by id desc) last_val,

                          first_value(id) over(partition by grp order by id desc) id_last_val

                          from (

                            select a.*,

                            last_value(case when operator = '#' then id end) ignore nulls over(order by id desc) grp

                            from t a

                          ) a

                          where operator != '#'

                        )

                        group by grp, id_last_val, last_val

                        order by grp

                      ), xmltable(replace(factor1,'/',' div '));

                       


                      IDFACTOR1FACTOR2 TOTAL
                        4   0.5   50    25
                      10   0.5   70    35
                      17   0.2   100    20

                       

                      Message was edited by: StewAshton

                      • 8. Re: Calculation
                        BluShadow

                        You really should refer people back to your previous thread(s) on this subject so they can see what you've already been asking and what solutions you've already been provided with:

                         

                        Last and Rest Value in a string

                        Formula Value Creation

                         

                        And then at least people will have a chance of not re-doing what has already been told to you before.

                        • 9. Re: Calculation
                          931711

                          Dear Blu,

                           

                          The questions i asked before didnt help me in finding out the exact solution .. so i had to post it again .. my apology

                           

                          Thanks & Regards,

                          BS.

                          • 10. Re: Calculation
                            Etbin

                            As already said: just for this formula structure (i.e. a division followed by one or two multiplications)

                            just a demo that it can be done

                             

                            with

                            table_one as

                            (select 1 id,'(' operator,null variable,null Value from dual union all

                            select 2,'(','A',10 from dual union all

                            select 3,'/','B',20 from dual union all

                            select 4,')',null,null from dual union all

                            select 5,'*','C',50 from dual union all

                            select 6,'*','D',10 from dual union all

                            select 7,')',null,null from dual union all

                            select 8,'#',null,null from dual union all

                            select 9,'+',null,null from dual union all

                            select 10,'(','D',50 from dual union all

                            select 11,'/','E',100 from dual union all

                            select 12,')',null,null from dual union all

                            select 13,'*','F',70 from dual union all

                            select 14,'#',null,null from dual union all

                            select 15,'+',null,null from dual union all

                            select 16,'(','G',80 from dual union all

                            select 17,'/','H',40 from dual union all

                            select 18,')',null,null from dual union all

                            select 19,'*','I',0.1 from dual union all

                            select 20,'*','J',100 from dual union all

                            select 21,'#',null,null from dual union all

                            select 22,'+','X',600 from dual union all

                            select 23,'#',null,null from dual union all

                            select 24,'-','Y',1100 from dual

                            )

                            select id,factor_1,factor_2,factor_1 * factor_2 total

                              from (select id,operator,value,

                                           case when operator = '*'

                                                then case when lag(operator) over (order by id) = '*'

                                                          then lag(factor_1,2) over (order by id) * lag(factor_2) over (order by id)

                                                          when lead(operator) over (order by id) = '#'

                                                          then lag(factor_1) over (order by id)

                                                          else factor_1

                                                     end

                                                else factor_1

                                           end factor_1,

                                           factor_2

                                      from (select id,operator,value,

                                                   case when operator = '/'

                                                         and lag(operator) over (order by id) = '('

                                                        then lag(value) over (order by id) / value

                                                        when operator = '*'

                                                        then 1

                                                        when operator in ('+','-')

                                                         and lag(operator) over (order by id) = '#'

                                                        then to_number(operator || '1')

                                                   end factor_1,

                                                   case when operator = '*'

                                                        then value

                                                        when operator in ('+','-')

                                                          and lag(operator) over (order by id) = '#'

                                                        then value

                                                   end factor_2

                                              from table_one

                                             where value is not null

                                                or operator = '#'

                                           )

                                   )

                            where operator in ('+','-','*')

                            order by id

                             

                            IDFACTOR_1FACTOR_2TOTAL
                            515050
                            62510250
                            13.57035
                            191.1.1
                            20.210020
                            221600600
                            24-11100-1100

                             

                            Regards

                             

                            Etbin

                            • 11. Re: Calculation
                              jihuyao

                              If the formula is defined simply as showed (each # group:  (?/?) *V1*V2*...*Vi), it may be worth a try like below.  Otherwise, a varying formula is just abuse to SQL although the design looks nice.  In that case, it had better to reconstruct the expression for each factor and get the values.

                               

                              below consider the case (?/?)*V1 with only one # group (for multiple groups set rownum rid for its own partition and assign each group a gid for recursive join)

                               

                              In case (?/?) *V1*V2*...*Vi), the recursive sql should not be much different considering only V1 part is recursively processed (and finally remap factor1 in the returned multiple * rows in that group).

                               

                              {code}

                                1  with tmp (rid, id, op, var, val, lvl, f1, f2, total, val1, val2) as

                                2  (select x.*, 1 lvl,

                                3  decode(x.operator, '*', 1, 1),

                                4  decode(x.operator, '*', x.value, 1),

                                5  0, 1, 1

                                6  from x where rid=1

                                7  union all

                                8  select x.rid, tmp.id, tmp.op, tmp.var, tmp.val, tmp.lvl+1,

                                9  tmp.f1,

                              10  tmp.f2,

                              11  null,

                              12  decode(x.operator, '(', x.value, tmp.val1),

                              13  decode(x.operator, '/', x.value, tmp.val2)

                              14  from tmp, x where x.rid=tmp.rid+1)

                              15  select

                              16  --*

                              17  id, val1/val2 f1, f2, (val1/val2)*f2 total

                              18  from tmp

                              19* where rid=id

                              SQL> /

                               

                                      ID         F1         F2      TOTAL

                              ---------- ---------- ---------- ----------

                                       4         .5         50         25

                               

                              SQL> desc xt

                              Name                                                                                  

                              --------------------------------------------------------------------------------------

                              ID                                                                                    

                              OPERATOR                                                                              

                              VARIABLE                                                                              

                              VALUE                                                                                 

                               

                              SQL> desc x

                              Name                                                                                  

                              --------------------------------------------------------------------------------------

                              RID                                                                                   

                              ID                                                                                    

                              OPERATOR                                                                              

                              VARIABLE                                                                              

                              VALUE                                                                                 

                               

                              SQL> select * from x ;

                               

                                     RID         ID O V      VALUE

                              ---------- ---------- - - ----------

                                       1          4 * C         50

                                       2          3 )

                                       3          2 / B         20

                                       4          1 ( A         10

                               

                              {code}