November 4, 2015 at 8:38 am
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement. Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names. Anyone have any insight as to how to do this? Thanks.
Gill
November 4, 2015 at 8:51 am
BadgerBully (11/4/2015)
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement. Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names. Anyone have any insight as to how to do this? Thanks.Gill
Storing formulas like this is always going to be a challenge. SQL Server is really great at storing and retrieving data. It is not great as a programming language replacement. The bigger challenge is that is seems you need or want some help with this but you didn't give us any information other than a very vague description of what you are trying to do. Please take a few minutes to read the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2015 at 2:44 pm
Something like this???
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
ColA INT,
ColB INT,
Colc INT,
Formula VARCHAR(100)
);
INSERT #temp (ColA,ColB,Colc,Formula) VALUES
(1,2,3,'(ColA + ColC) * ColB'),
(11,22,33,'ColA + ColB + ColC')
;
SELECT
t.ColA,
t.ColB,
t.Colc,
t.Formula,
REPLACE(REPLACE(REPLACE(t.Formula, 'ColA', CAST(t.Cola AS VARCHAR(10))), 'ColB', CAST(t.ColB AS VARCHAR(10))), 'ColC', CAST(t.ColC AS VARCHAR(10))) AS UpdatedFormula
FROM
#temp t
;
November 5, 2015 at 7:37 am
Yes - something like that - thanks - now to figure out how to do it dynamically since I don't know ahead of time which columns I will be using in the equation
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply