# I need a PL-SQL querey which can do any kind of arithmetic operations.

I have 3 tables Formula , Input, Output. I have a expression column in the formula table which has all the arithmetic expressions.
For eg: (x+2y-3z+(2a/4)-3c)*100... I need to read this formula then substitute the values of X,Y,Z,A,C from input table and after execution I need to store the output value in the output table.

I have writen a query which can do the math for 2 variables and for single operand for eg : x+2y =z. But I dont have any idea how to do or write query for multi operand and for multi variabes as stated above.

If anyone has any idea of how to achieve this. It would be really helpfull to me. Please check the attached create table quereis and the sample query for your reference.

Regards
Gowtham
CreateQuery-s.txt
qUEREY2.txt
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Nothing past obvious:
select (x+2*y-3*z+(2*a/4-3*c*100) from data;
Author Commented:
This is not I require. I need to do the mathematical calculation dynamically with the query
Commented:
Excuse me? x y z and a abd c can only be selected from a table.
Can you show "better" way to run arithmetic query in SQL?
Commented:
You have to 2 this in 2 steps:

1. Use the tables to replace in the formulas the variables with their values from Input table and to save the formula with values in the output tables. In order to do this you will have to add a new column in the Output table called outputexpression. This will be done using a CTE query because it needs 2 steps to make the code more readable.

2. Loop through the Output table and execute the expressions from the outputexpression one by one and update the values into the outputvalue column. This will be done using the sp_executesql system stored procedure to execute the expression and grab the value into a variable.

In order to make the replace process of the variables in the formulas with the values correctly you will need to do 2 changes to the formula expressions:

1. Remove from the formula the part that is before/after the = sign corresponding to the rezult part. That is not needed because it will be in the output table anyway and in order to actually calculate/execute the expression only the part that defines the expression is needed. I.e, in order to execute d = ( a + b ) * c  you need only  ( a + b ) * c part and then you will save the result in d.

2. IMPORTANT! Separate the ALL elements (variable names, operands and brackets) in a formula with spaces, even at the beginning at the end of formula! I.e. '(a+b)*c' should be ' ( a + b ) * c ' This will ensure that the variables will be replace as whole words only and not partially. Also In the execution script I add space before and after each variable and value.

I will attach 2 files:
1. The create statements with the modifications as I mentioned: _dynamic_expressions_create_query.sql
2. The actual script that will execute formulas and save them in the output table: _dynamic_expressions_execute.sql
_dynamic_expressions_create_query.sql
_dynamic_expressions_execute.sql

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
OMG this is an excellent answer.... I was struggling to get this for more than a week...Thanks a lot for the help.
Author Commented:
Hi ,

with the above example slightly modified the input column in Formula table. I need the same output. Is this possible ?
Modified part is attached with the create table queries.

Now i have a dynamic situation that all the input values are coming in a single column. I need to fetch those values from a table using diffrent joins in diffrent tables.

After fetching those values I'm not sure how to replace that in the formula .
Can you please guide me on this one.
CreateTableVersion2.txt
Commented:
Easiest is to start a new thread...
Author Commented:
what is a new thread ?
Commented:
"what is a new thread ?"

Ask a new question. You could refer to it (post a link) in here.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.