Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Execute some basic math in a single SQL

Re: Execute some basic math in a single SQL

From: Nigel Thomas <>
Date: Tue, 27 Mar 2007 05:31:55 -0700 (PDT)
Message-ID: <>

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

Received on Tue Mar 27 2007 - 07:31:55 CDT

Original text of this message