SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Any help solidifying this...? (dbForums X-Post)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 09/15/2004 :  16:32:11  Show Profile
I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.

Table Structure

FORMULA
ID | Descr
---------------
1  | Rectangle

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
--------------------------------------
1     | Side A     | X          | 
1     | Size B     | Y          | 
1     | Height     | Z          |
1     | Area       | A          | X * Y
1     | Volume    | V         | A * V

FORMULA_VARIABLE_VALUE
fkID | Variable | Value
----------------------
1     | X          | 10
1     | Y          | 10
1     | Z          | 2


In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.

Notice the Volume expression uses the expression for the variable A. This is where the fun begins....

I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.

The code is a first time run-through!


USE Northwind
GO

CREATE TABLE FORMULA
(
  FormulaID int NOT NULL,
  Descr char(30)
)
GO

CREATE TABLE FORMULA_VARIABLE
(
   fkFormulaID int NOT NULL,
   Dimension char(30),
   Variable char(10),
   Units char(5),
   Expression char(255)
)
GO

CREATE TABLE FORMULA_VARIABLE_VALUE
(
   fkFormulaID int,
   Variable char(10),
   Value float
)
GO

INSERT INTO FORMULA (FormulaID, Descr)
SELECT 1, 'Rectangular'
GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)
SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL
SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL
SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL
SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL
SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO
GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)
SELECT 1, 'X', 10 UNION ALL
SELECT 1, 'Y', 10 UNION ALL
SELECT 1, 'Z', 2
GO

CREATE PROCEDURE usp_BuildExpressions 
@iFormula int
AS

CREATE TABLE #TempFormulaResults
(
	Dimension char(30),
	Value float
)

DECLARE @cDimension char(30), @cOldExp varchar(255), @cNewExp varchar(4000), @cVariable char(5)

DECLARE OldExpCursor CURSOR FOR
	SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULL
OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @cDimension, @cOldExp
WHILE (@@FETCH_STATUS = 0)
	BEGIN
        -- Iterate through expresions, build new cursor where dimension is not equal
	-- Replace expression variable with expressions
	DECLARE NewExpCursor CURSOR FOR
		SELECT Variable, Expression FROM FORMULA_VARIABLE 
		WHERE fkFormulaID = @iFormula AND Expression IS NOT NULL AND NOT (Dimension = @cDimension)
	
	OPEN NewExpCursor
	FETCH NEXT FROM NewExpCursor INTO @cVariable, @cNewExp
	WHILE (@@FETCH_STATUS = 0)
		BEGIN
		SELECT @cOldExp = REPLACE(RTRIM(@cOldExp), RTRIM(@cVariable), RTRIM(@cNewExp))
		FETCH NEXT FROM NewExpCursor INTO @cVariable, @cNewExp
		END
	CLOSE NewExpCursor
	DEALLOCATE NewExpCursor
	
	-- Get the variable values, replace values in expression and calcluate result
	DECLARE @fValue float, @cVarName char(5)
	DECLARE ValueCursor CURSOR FOR
		SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE
		WHERE fkFormulaID = @iFormula
	
	OPEN ValueCursor
	FETCH NEXT FROM ValueCursor INTO @cVarName, @fValue
	WHILE(@@FETCH_STATUS = 0)
		BEGIN 
		SELECT @cOldExp = REPLACE(@cOldExp, RTRIM(@cVarName), CONVERT(VARCHAR, @fValue))
		FETCH NEXT FROM ValueCursor INTO @cVarName, @fValue
		END	
	
	DECLARE @cSelect nvarchar(4000), @param nvarchar(4000), @Eval float

	SET @cSelect = 'SET @fResult = ' + @cOldExp
	SET @Param = '@fResult float OUTPUT'
	
	EXEC sp_executesql @cSelect, @Param, @Eval OUT
	INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@cDimension, @Eval)
	FETCH NEXT FROM OldExpCursor INTO @cDimension, @cOldExp
	CLOSE ValueCursor
	DEALLOCATE ValueCursor
	END

CLOSE OldExpCursor
DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults
DROP TABLE #TempFormulaResults
GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA
GO
DROP TABLE FORMULA_VARIABLE
GO
DROP TABLE FORMULA_VARIABLE_VALUE
GO
DROP PROCEDURE usp_BuildExpressions


Any thoughts?

Mike B

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 09/16/2004 :  07:09:19  Show Profile
On flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?


FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
--------------------------------------
1     | Side A     | X          | 
1     | Size B     | Y          | 
1     | Height     | Z          |
1     | Area       | A          | X * Y
1     | Volume    | V         | A * Z
1     | 1/2 Vol   | v         | V / 2


Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 09/16/2004 :  08:17:55  Show Profile  Visit robvolk's Homepage
I'd say that this is something that doesn't benefit from being in a database. SQL is not a mathematical language, and it's not good at expression substitution and evaluation. What I think would work better is to evaluate the expression outside of the database...using a separate program like VB, C#/C++, or Java/JavaScript. You can still store the expressions in the database and retrieve them when needed, but the actual calculations and presentation should be done separately.
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 09/16/2004 :  08:28:44  Show Profile
quote:
Originally posted by robvolk

I'd say that this is something that doesn't benefit from being in a database. SQL is not a mathematical language, and it's not good at expression substitution and evaluation. What I think would work better is to evaluate the expression outside of the database...using a separate program like VB, C#/C++, or Java/JavaScript. You can still store the expressions in the database and retrieve them when needed, but the actual calculations and presentation should be done separately.


I thought of this, and I do believe you are right in a sense, but I need the results of the expressions in reporting which is done from applications such as Crystal Reports...

Should I just compute the results and then store then in a table?

I am just trying to follow the rules of thumb of Codd, "No computed values should be stored!".

Even if I did store the computed values, I would still like to get this working. Would you have any ideas?

Mike B
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 09/16/2004 :  08:34:11  Show Profile  Visit robvolk's Homepage
You *might* be able to pass the expressions to Crystal Reports and have it evaluate it (I'm pretty sure there's an EVAL() function or something like it) It will probably be pretty difficult though.

The only other thing I can suggest is to write pre-defined queries or UDF's that do the actual evaluation in SQL Server, instead of storing the formula in a column. You'll end up with a lot of functions, and it won't provide the control that a user might want (DO NOT let end users create UDFs) but it will greatly simplify the SQL you need to write for the reports you need.
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 09/16/2004 :  08:47:56  Show Profile
quote:
Originally posted by robvolk

(DO NOT let end users create UDFs)


This is the major problem that I am trying to solve. I do not know what functions the user will actually want. All the formulas are to be user defined.

I think what I will do is setup a table that stores all the variable values for the computed values. When the user updates a variable in the front end, the front end will run through the forumulas and update this table. This table will hold the user entered variable values as well as the computed values.

The front end is written in C++ so I am sure I can come up with something there that will accurately calculate the results.

Mike B
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 09/16/2004 :  13:10:45  Show Profile
I know this is not much help, but within the last 3 months, here or at experts-exchange, I saw a scheme for implementing formulas for pricing that I was really impressed with. I have implemented something similar and remembered thinking how clever and versital the scheme I saw was. I thought I had bookmarked it but I can't find it again. I guess I'm mentioning it in case this tweeks anyone elses memory.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000