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 
Solomon Yakobson
Messages: 3066 Registered: January 2010 Location: Connecticut, USA

Senior Member 


The purpose of this puzzle is to wrapup 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:
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: 3066 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 
Solomon Yakobson
Messages: 3066 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 
Solomon Yakobson
Messages: 3066 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 
Solomon Yakobson
Messages: 3066 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.



Goto Forum:
Current Time: Tue Jul 27 19:21:08 CDT 2021
