Evaluate math expression using sql

[dsarda]

I have a formula (e.g. :a*:b) as a record in a table. In my stored procedure, I retrieve this formula and replace ':a' and ':b" with the appropriate values. Now I want to insert the result of this expression (a*b) into a table.

I have tried a few approaches and they result in the expression being inserted (e.g. 300*12) and not the result of the expression (e.g. 3600) being inserted.

Any ideas how to fix this?

Thanks in advance

 

[JediDan]

I'm curious why you're storing a formula in a table?

If you create a function to handle the math, you can use the function call in the VALUES part of the INSERT statement.

Example:

function double(x integer) return integer is
begin
return x*2;
end;

INSERT INTO myTable
VALUES (double(2));

This will result in an insert of 4.

Hope this helps!

-Dan

 

[dsarda]

Dan,
I have to calculate a value and the formula for it's calculation changes in terms of expression and data required depending on certain business rules.

e.g:
sum = a*b + c if business rule 1 is met
sum = a + d if business rule 2 is met

and so on and so forth.

Thanks
-- dnyanesh

 

[JediDan]

Well in that case it sounds like you would need multiple functions which are called from the procedure.

Example:

if (testvariable = 1) then
 result := function_nbr1(a,b,c);
elsif (testvariable = 2) then
 result := function_nbr2(a,d);
end if;

Or you can use a CASE statement if testvariable will have many values.

You can eliminate the functions all together and just put the math right in the

if

section. I prefer to put all business logic inside a separate function (in most cases).

Hope this helps, if not, post your code, I'll take a look.

-Dan

 

[dsarda]

Dan,
The formulae are numerous and required to be dynamic so that system administrators can keep adding new ones as and when required without having to make changes to the procedure.

This is the code snippet:
_____________________________________________________
select replace(replace(replace(cta_etl_formula,':RMONTHS',to_char(rc_rmonths)),':CKTAGE',to_char(rc_cktage)),':MRC',to_char(rc_mrc)),cta_etl_formula into rc_etl, rc_etl_formula from ref_etl_rule where cta_vendor_id=i_cta_vendor_id and line_speed_id=i_line_speed_id and rc_term_length >=min_term_range and rc_term_length <= max_term_range;

rc_etl_note := 'ETL Formula is:' || rc_etl_formula;

select rc_etl into rc_etl from dual;
--------------------------------------------------
I select cta_etl_formula into rc_etl after replacing the rmonths, cktage and mrc variables - I am just looking for a simpler way to evaluate the math expression I get (300*12+34) into a variable.

Ofcourse, another way for me is to get the cta_etl_formula without the replace functions in sql and use dbms sql - i am just looking for something quicker and simpler.

Thanks
-- dnyanesh

 

[JediDan]

Don't forget you can use EXECUTE IMMEDIATE, which is a lot easier than the process of setting up and using a DBMS_SQL cursor.

Some info I found one time on EI:

http://databasejournal.com/features/oracle/article.php/2109681

Looking at your code, I would still think there's a way to simplify it, but I don't think I will be able to say what that simplification process is being without seeing everything that you're doing, which is probably too much to post.

 

[dsarda]

Thanks Dan - I tried execute immediate - however i cannot use the using clause to bind variables because the number and order of variables is dynamic depending on the scenario.

i think i am just going to parse the sql and bind variables if they exist in the formula and then execute immediate into my value variable.

thanks again
-- dnyanesh

 

[SantaMufasa]

DSarda,

You can try this method for which you need neither &quot;CASE&quot; / &quot;IF&quot; testing nor special functions:

SQL> set serveroutput on
SQL> exec dbms_output.put_line(':a='||:a);
:a=3
SQL> exec dbms_output.put_line(':b='||:b);
:b=5
SQL> select * from formula;

X
---------------------------------------------------------------
:a * :b
SQL> declare
  2   formula_hold varchar2(100);
  3   answer  number;
  4   statement varchar2(100);
  5  begin
  6   select x into formula_hold from formula where rownum = 1;
  7   formula_hold := replace(formula_hold,':a',:a);
  8   formula_hold := replace(formula_hold,':b',:b);
  9   statement := 'select '||formula_hold||' from dual';
 10   execute immediate statement into answer;
 11   insert into dsarda values (answer);
 12  end;
 13  /
SQL> select * from dsarda;

         X
----------
        15
SQL>

Let me know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:42 (02Jan04) GMT, 14:42 (02Jan04) Mountain Time)

 

[dsarda]

Mufasa,
That worked fine - Thank you very much !

-- dnyanesh