 Home » Other » General » Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas ***
Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas *** Mon, 05 November 2012 11:56 Solomon Yakobson Messages: 3008Registered: 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  Solomon Yakobson Messages: 3008Registered: 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  Solomon Yakobson Messages: 3008Registered: 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  Solomon Yakobson Messages: 3008Registered: 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 Solomon Yakobson Messages: 3008Registered: 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: Sun Nov 29 23:34:26 CST 2020