Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Any help solidifying the following?

    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
    Code:
    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!

    Code:
    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

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •