Mark wrote:
I have data that is a number and a formula – one row would look like this:
select 2 num, 'X + X' formula from dual
The formula has 1 variable only but it can be repeated more than once.
with data_looks_like_this as (select 2 num, 'X + X' formula from dual)
select num, formula, replace(formula, 'X', num) f from data_looks_like_this
Column f now has 2 + 2
Can I possibly get a 4?
----
You need to have the formula as a SQL expression visible to the parser, not as a string.
You can set the variables in an inline query, and use column aliases to display the formula:
SQL> r
1 select x "x", y "y", z "z", x+y "x+y", x+2*y+z "x+2*y+z"
2* from (select 1 x, 2 y, 3 z from dual)
x y z x+y x+2*y+z
---------- ---------- ---------- ---------- ----------
1 2 3 3 8
However the formulas themselves are displayed as select expression aliases, rather than in the row as results... is that good enough? Note the column alias is treated as an identifier - limited to 30 characters.
If you want to do this in bulk you could read a sequence of variable name+value pairs, and then any number of formulae, and then construct SQL like the one above to evaluate the expression(s). Or construct PL/SQL blocks like this:
SQL> var result1 number -- bind variables for results generated from formulas; 'result1' could be the formula name (eg 'gross_pay')
SQL> var result2 number
SQL> var result3 number
SQL> declare
2 x number := 1; -- declarations generated from name/value pairs for inputs
3 y number := 2;
4 z number := 3;
5 begin
6 :result1 := x+y; -- assignments generated from formulas
7 :result2 := x+y;
8 :result3 := x+2*y+z;
9* end;
SQL>/
PL/SQL procedure successfully completed.
SQL> print :result1 -- results to print also generated from formulas
RESULT1
----------
3
SQL> print :result2
RESULT2
----------
3
SQL> print :result3
RESULT3
----------
8
In a real 3GL like C or PL/SQL, you can easily iterate this, and tart up the output - but then you'd have to call it Fast Formula :-) - this is roughly how Oracle HR's formulas originally worked late 1980s - and still is, as far as I know.
Regards Nigel
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 27 2007 - 07:31:55 CDT
