Calculate a Math formula in a column

You will need to do this as dynamic SQL as far as I know.

DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = 'SELECT ''' + FormulaColumnName + ''' AS FORMULA, CONVERT(MONEY, ' + FormulaColumnName + ',0) AS RESULT FROM DataTableName' 
FROM FormulaTableName
-- Use where clause to filter to only one formula if multiple
--WHERE SomeColumn = 'SomeCondition'
 
EXEC(@SQL); -- execute dynamic sql

Select allOpen in new window

mwv: Where this will break is if the forumla can be different for various columns.  

?? Not sure it will, but if you say so.

The SQL I am constructing should come out something like this:

SELECT '[AMT]*[FX_RT]*[VAT_RT]' AS FORMULA
, CONVERT(MONEY, [AMT]*[FX_RT]*[VAT_RT],0) AS RESULT
FROM DataTableName

As long as the formula has valid column names and mathematical operators that the above SQL query would run correctly then NOT sure what the issue would be.  Would have to defer to Brandon on an example.

Regards,
Kevin

But I believe that the Formula is, if I understand correctly, a column within the table containing the data.  So there's probably not a formula table, but the formula is contained within the "Formula" column.

>>I have a table that holds formulas, for example [AMT]*[FX_RT]*[VAT_RT]. I need to select from this table and add a new column containing the results.

Ok, i see what you are saying now, Brandon.  I was going off of the above.  And my interpretation was that table holding formula had string data that represented column names and mathematical operators for data in some other table that has columns in this example of [AMT], [FX_RT], and [VAL_RT].  If this is in the same table, then should still work with dynamic sql; however, that wouldn't work if these formulas are in computed columns if that is what you are meaning Brandon.

Yes, correct. Here is exactly what I have.
I have a table with data, for example, AMT, FX_RT, VAT_RT, etc. The table also contains 3 other elements: A Project, a InvCode and a TypeCode. Based on these 3 values I can join to my "Formula" table. For every match in he Formula table, calculate the result. So for example. Project 'AA', InvCode 'D' and TypeCode 'Q' has 3 Formula rows.

Thanks, I am off for the day but will look at this later this evening. Really appreciate all of your help and ideas.
Hogg

It's fairly easy to get from :
[AMT]*[FX_RT]*[VAT_RT]
to:
'10000*.875*.0524879'

The trick is how to evaluate that string as an expression and return the result.

That will likely be MUCH easier in a CLR function than in SQL Server itself.

I know, for example, a lot of people use an EVAL() in VB to do it (at least I used to use one I created back in worked (a LITTLE) with VB (and even back in QuickBasic days)).

ooopppssss - didn't see the datatable being seperate - must read more carefully he says sheepishly.... I'll be back

>> the trouble being replacing column names with values in the formula <<

Actually, to me, that's the *easy* part, as I said.

The *hard* is finding an efficient way to evaluate the resulting expression.  I don't know of how to do that easily in SQL.

[I STRONGLY wish SQL would an EVAL() function: I do *not* need it to support variables or group functions (AVG(), etc.), just arithmetic expressions and basic functions (SQRT(), ROUND(), etc.).]

Just working on adding in the brackets now - wasn't there in the old one...

I think the UDF is the approach that is generic enough. Like was mentioned earlier by Scott, this is the perfect description of the gap. I would consider CLR if it was not like driving a truck through a china shop to get it done at my company.
It's fairly easy to get from :
[AMT]*[FX_RT]*[VAT_RT]
to:
'10000*.875*.0524879'

The trick is how to evaluate that string as an expression and return the result.

So, what if I was to give you a function that can do things like:   select dbo.udf_Maths(mystr)     -- where mystr = '1+3*(5-1)/8'
That is the only thing I see that will work for my issue. I am going to end this question and open a new one for the UDF code.
Thanks All!
Hogg

Are there any brackets ? :) Them there things are damned tricky, but on a mission now.

I am not quite as confident about the ease of replacing strings within a column with their value equivalent - can use a biggish "case" but I am imagining that there is array logic lurking to recurse the column to extract component columns and reassemble with string values and operators.

By the way, at the moment, I can only handle simple operators "+"  "-"  "*"  "/"

No, do not end this one, just post the code to do the interpretaion of the formula from column names into string values.

More than happy to answer an SQL EVAL() function seperately, but feel that it should be kept together - besides I need more time to finish (or kill the bracket option).

OK, I opened the new question.
Are there any brackets ? :) Them there things are damned tricky, but on a (LOL) mission now.
I don't think I need brackets for this project, but I see value in the use of them - as do you I am sure.

I am not quite as confident about the ease of replacing strings within a column with their value equivalent - can use a biggish "case" but I am imagining that there is array logic lurking to recurse the column to extract component columns and reassemble with string values and operators.
I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?

By the way, at the moment, I can only handle simple operators "+"  "-"  "*"  "/"
Great! That is awesome.

>> I don't think I need brackets for this project, but I see value in the use of them - as do you I am sure. <<

Yes, that would be the huge gain of having the SQL (or VB) engine itself do the calcs; functions would also then be available (SQRT(), etc.).

I wrote something like that (for SQL 7.0) but it required sending every value thru sp_executesql (so that SQL could do the evaluation, enabling parens, functions, etc.).  Here, naturally, that would require cursoring thru the formulas -- :-( :-( .

A function will be my choice. Thanks to all who helped work through this one. I have opened a new question to walk through the function approach.
https://www.experts-exchange.com/questions/24141221/User-Defined-Function-to-evaluate-a-string-containing-an-expression-and-return-the-result.html?anchorAnswerId=23633856#a23633856

I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?
Yes, but I think Scott has already achieved that and I wanted to build a generic routine and was contemplating adding a table name as an argument. But I will not worry.
I will play with brackets tonight (currently 2:30am "downunder") - might need to limit their use, and Scott, will try to consider the "ownership" of brackets for inclusion of other functions, but, you might need to jump in on including those...

I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?
Yes, but I think Scott has already achieved that and I wanted to build a generic routine and was contemplating adding a table name as an argument. But I will not worry.
Now we are talking. :-)
That would be AWESOME!!!

Might post without the brackets, and then work from there :)

Mark, Go to bed! :-)