1

I want to know what is there a way for storing equations or formula in a DB column. Below is the table where I am storing the machines details(master table). But the last column is what I am worrying.

Columns and values for the table would be

id,name,col1,col2,col3,col4,formula
1,mc1,546,459,1.24,3.9,(col1 * col2 * 0.0016)/(col3 * col4)

The formula changes for each entry in the table. Currently I am hard-coding the formula's in my program like

if(macid == 1) {
 // apply formula1
} else if(macid == 2) {
 // apply formula2
} .....

Now if any new machine gets added in the table I need to make changes int the code. So I want to store the formula in the table itself, so that I can provide UI interface to change the formula.

How can I achieve this. Plese help.

  • How badly do formulas vary? Can you add a few more real examples? – peterm Nov 26 '13 at 6:35
  • second machine may have, another formula like, (col1 * 2 * 0.0016)/(col 3 * col4). The problem is when i want to add a new machine to the table and user wants to enter the formula and save it for that from the UI. Now i need to add one more else if in my code. – Nakul Sargur Nov 26 '13 at 9:06
  • See my answer. Dynamic SQL is the only solution I can possibly think of when you potentially have different formulas for every row – peterm Nov 26 '13 at 9:12
1

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
  SET @sql = NULL;

  SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
    INTO @sql
    FROM table1
   WHERE id = 1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

|           VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
    'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
    ORDER BY id SEPARATOR ' UNION ALL ')
    INTO @sql
    FROM table1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Sample usage:

CALL get_value(1);

Sample output:

| ID |           VALUE |
|----|-----------------|
|  1 | 82.916129032258 |
|  2 |    0.0000109375 |

Here is SQLFiddle demo for both procedures

  • Did it help? Do you need more help with your question? – peterm Nov 27 '13 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.