Home » Other » General » Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas ***
Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas *** [message #570101] Mon, 05 November 2012 11:56 Go to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
The purpose of this puzzle is to wrap-up all SQL or PL/SQL techniques to calculate hardcoded arithmetic expressions and formulas. Hadcoded arithmetic expression is a string storing any arithmetic expression where operands are constants. For example:

5 + 7
(9 - 3) * (5 + 3)


Formula is also a string storing any arithmetic expression where operands are either constants or variables. Variable values are provided separately. To calculate formula we need to substitute variables referenced in the formula with their values and then calculate resulting arithmetic expression. Formula examples:

a + b
a + b - 100
(a + b) * 100 / d


SY.



[EDITED by LF: added puzzle number to the topic title. I can't decide how difficult it is. I hope someone else will be kind and supply that piece of information.
Edit MC: 3 stars given the definition we defined of star]

[Updated on: Tue, 06 November 2012 01:47] by Moderator

Report message to a moderator

Re: Calculating hardcoded arithmetic expressions and formulas [message #570105 is a reply to message #570101] Mon, 05 November 2012 12:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Package DBMS_AW based solution
------------------------------


This solution requires Oracle supplied DBMS_AW package which comes with OLAP option. You will not be able to use this solution if OLAP option is not installed.

Expressions
-----------


with expressions as (
                     select '2 * 3' expr from dual union all
                     select '(2 + 3) * 5' expr from dual
                    )
select  expr || ' = ' || dbms_aw.eval_number(expr) result
  from  expressions
/

RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25

SQL> 


Formulas
--------


This solution uses, besides Oracle supplied DBMS_AW package, MODEL clause and therefore will not work prior to Oracle 10:

with formulas as (
                  select 1 id, 'a+ab' frm from dual union all
                  select 2, 'a*c+bc' from dual
                 ),
    variables as (
                  select 1 id, 'a' var, 2 val from dual union all
                  select 1, 'ab', 3 from dual union all
                  select 2, 'a', 6 from dual union all
                  select 2, 'bc', 7 from dual union all
                  select 2, 'c', 9 from dual
                 )
select  id,
        frm || ' = ' || expr || ' = ' || dbms_aw.eval_number(expr) result
  from  (select  id,
                 frm,
                 expr,
                 r
           from  formulas f,
                 variables v
           where v.id = f.id
           model
           partition by(f.id id)
           dimension by(row_number() over(partition by f.id order by 1) r)
           measures(
                    frm,
                    regexp_replace(frm,'(\w+)',' \1 ') expr,
                    ' ' || var || ' ' var,
                    val
                   )
           rules(
                 expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
                )
        )
  where r = 1
/

        ID RESULT
---------- ----------------------
         1 a+ab = 2+3 = 5
         2 a*c+bc = 6*9+7 = 61

SQL> 


SY.
Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570106 is a reply to message #570101] Mon, 05 November 2012 12:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member

XMLQUERY based solution
-----------------------


Requires Oracle 11.

Expressions
-----------


with expressions as (
                     select '2 * 3' expr from dual union all
                     select '(2 + 3) * 5' expr from dual
                    )
select  expr || ' = ' || xmlcast(xmlquery(expr returning content) as number) result
  from  expressions
/

RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25

SQL> 


Formulas
--------


with formulas as (
                  select 1 id, 'a+ab' frm from dual union all
                  select 2, 'a*c+bc' from dual
                 ),
    variables as (
                  select 1 id, 'a' var, 2 val from dual union all
                  select 1, 'ab', 3 from dual union all
                  select 2, 'a', 6 from dual union all
                  select 2, 'bc', 7 from dual union all
                  select 2, 'c', 9 from dual
                 )
select  id,
        frm || ' = ' || expr || ' = ' || xmlcast(xmlquery(expr returning content) as number) result
  from  (select  id,
                 frm,
                 expr,
                 r
           from  formulas f,
                 variables v
           where v.id = f.id
           model
           partition by(f.id id)
           dimension by(row_number() over(partition by f.id order by 1) r)
           measures(
                    frm,
                    regexp_replace(frm,'(\w+)',' \1 ') expr,
                    ' ' || var || ' ' var,
                    val
                   )
           rules(
                 expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
                )
        )
  where r = 1
/

        ID RESULT
---------- ----------------------
         1 a+ab = 2+3 = 5
         2 a*c+bc = 6*9+7 = 61

SQL> 


SY.
Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570109 is a reply to message #570101] Mon, 05 November 2012 12:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
DBMS_XMLGEN based solution
--------------------------



Expressions
-----------


with expressions as (
                     select '2 * 3' expr from dual union all
                     select '(2 + 3) * 5' expr from dual
                    )
select  expr || ' = ' ||
          dbms_xmlgen.getxmltype(
                                 'select ' || expr || ' x from dual'
                                ).extract('/ROWSET/ROW/X/text()').getnumberval() result
  from  expressions
/

RESULT
------------------
2 * 3 = 6
(2 + 3) * 5 = 25

SQL> 



Formulas
--------


with formulas as (
                  select 1 id, 'a+ab' frm from dual union all
                  select 2, 'a*c+bc' from dual
                 ),
    variables as (
                  select 1 id, 'a' var, 2 val from dual union all
                  select 1, 'ab', 3 from dual union all
                  select 2, 'a', 6 from dual union all
                  select 2, 'bc', 7 from dual union all
                  select 2, 'c', 9 from dual
                 )
select  id,
        frm || ' = ' || expr || ' = ' ||
          dbms_xmlgen.getxmltype(
                                 'select ' || expr || ' x from dual'
                                ).extract('/ROWSET/ROW/X/text()').getnumberval() result
  from  (select  id,
                 frm,
                 expr,
                 r
           from  formulas f,
                 variables v
           where v.id = f.id
           model
           partition by(f.id id)
           dimension by(row_number() over(partition by f.id order by 1) r)
           measures(
                    frm,
                    regexp_replace(frm,'(\w+)',' \1 ') expr,
                    ' ' || var || ' ' var,
                    val
                   )
           rules(
                 expr[any] order by r desc = regexp_replace(nvl(expr[cv() + 1],expr[cv()]),var[cv()],val[cv()])
                )
        )
  where r = 1
/

        ID RESULT
---------- ---------------------
         1 a+ab = 2+3 = 5
         2 a*c+bc = 6*9+7 = 61

SQL> 


SY.
Re: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas [message #570118 is a reply to message #570101] Mon, 05 November 2012 14:39 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Recursive subquery factoring based solutions for formulas
---------------------------------------------------------


Requires Oracle 11.2:

with formulas as (
                  select 1 id, 'a+ab' frm from dual union all
                  select 2, 'a*c+bc' from dual
                 ),
    variables as (
                  select 1 id, 'a' var, 2 val from dual union all
                  select 1, 'ab', 3 from dual union all
                  select 2, 'a', 6 from dual union all
                  select 2, 'bc', 7 from dual union all
                  select 2, 'c', 9 from dual
                 ),
            t as (
                  select  id,
                          ' ' || v.var || ' ' var,
                          val,
                          row_number() over(partition by id order by 1) rn,
                          count(*) over(partition by id) cnt
                    from  variables v
                 ),
          r(
            id,
            frm,
            expr,
            lvl,
            cnt
           ) as (
                  select  id,
                          frm,
                          regexp_replace(frm,'(\w+)',' \1 '),
                          0,
                          -1
                    from  formulas
                 union all
                  select  r.id,
                          r.frm,
                          regexp_replace(r.expr,t.var,t.val),
                          r.lvl + 1,
                          t.cnt
                    from  r,
                          t
                    where t.id = r.id
                      and t.rn = r.lvl + 1
                )
select  id,
        frm || ' = ' || expr || ' = ' ||
           dbms_aw.eval_number(expr) result
  from  r
  where lvl = cnt
/

        ID RESULT
---------- --------------------
         1 a+ab = 2+3 = 5
         2 a*c+bc = 6*9+7 = 61

SQL> 


with formulas as (
                  select 1 id, 'a+ab' frm from dual union all
                  select 2, 'a*c+bc' from dual
                 ),
    variables as (
                  select 1 id, 'a' var, 2 val from dual union all
                  select 1, 'ab', 3 from dual union all
                  select 2, 'a', 6 from dual union all
                  select 2, 'bc', 7 from dual union all
                  select 2, 'c', 9 from dual
                 ),
            t as (
                  select  id,
                          ' ' || v.var || ' ' var,
                          val,
                          row_number() over(partition by id order by 1) rn,
                          count(*) over(partition by id) cnt
                    from  variables v
                 ),
          r(
            id,
            frm,
            expr,
            lvl,
            cnt
           ) as (
                  select  id,
                          frm,
                          regexp_replace(frm,'(\w+)',' \1 '),
                          0,
                          -1
                    from  formulas
                 union all
                  select  r.id,
                          r.frm,
                          regexp_replace(r.expr,t.var,t.val),
                          r.lvl + 1,
                          t.cnt
                    from  r,
                          t
                    where t.id = r.id
                      and t.rn = r.lvl + 1
                )
select  id,
        frm || ' = ' || expr || ' = ' ||
           xmlcast(xmlquery(expr returning content) as number) result
  from  r
  where lvl = cnt
/

        ID RESULT
---------- --------------------
         1 a+ab = 2+3 = 5
         2 a*c+bc = 6*9+7 = 61

SQL> 


SY.
Previous Topic: when user trying to connect from clent side it says no longer available
Next Topic: Puzzle n°07 - Create A Calendar for the Given Month and Year *
Goto Forum:
  


Current Time: Fri Mar 29 03:08:27 CDT 2024