Re: Execute some basic math in a single SQL

Hi,

> 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 may use execute immediate (e.g. in a function):

begin
declare
num number := 2;
variable_name varchar2(100) := 'X';
formula varchar2(2000) := 'X + X';
res number;
sql_text varchar2(2000);
begin
sql_text :=
'select '||replace( formula , variable_name,num) ||' from dual'; dbms_output.put_line ('sql = '|| sql_text); execute immediate sql_text INTO res;
dbms_output.put_line ('X = '|| res);
end;
end;
sql = select 2 + 2 from dual
X = 4

Jaromir

--
http://www.freelists.org/webpage/oracle-l