# Applying Equation to Decimal Values In Query

• Dec 1, 2009, 20:00
oddz
Applying Equation to Decimal Values In Query
Would it be possible to replace the ? with the price and calculate new price as a result of the calculation given the below scenario in a query? Essentially I'm looking for some way to eval() in SQL I believe…

I have a client who would like to be able set dynamic pricing on products based on equations being applied to the distributors price of the product. However, if I handle this in the application language I loose mySQL sorting and grouping abilities that I really need to calculate price ranges and what not. So this seems like the *best* idea…

thanks

Code SQL:

```CREATE TABLE prices ( prices_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,price DECIMAL(20,2) NOT NULL DEFAULT '0.00' ,PRIMARY KEY(prices_id) ) ENGINE=MyISAM DEFAULT CHARSET = utf8;   CREATE TABLE formulas ( formulas_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,formula VARCHAR(96) NOT NULL ,PRIMARY KEY(formulas_id) ) ENGINE=MyISAM DEFAULT CHARSET = utf8;   INSERT INTO prices (price) VALUES ('20.00'),('40.00'),('60.00'),('100.00')   INSERT INTO formulas (formula) VALUES ('?+20.00'),('?*2.00'),('?-5.00')   SELECT p.price ,f.formula ,'???' AS RESULT FROM prices p CROSS JOIN formulas f;```
• Dec 1, 2009, 20:06
Dan Grossman
Maybe in a stored procedure, since you can concatenate strings and execute them as a query there, I don't think so in just a SELECT...
• Dec 1, 2009, 20:13
oddz
So this would yield exactly what I need in terms of an equation. However, I'm not certain if its even possible to execute it… Oh eval() were are thou

Code SQL:

```SELECT REPLACE(f.formula,'?',p.price) AS pricing_calculation FROM prices p CROSS JOIN formulas f;```
• Dec 1, 2009, 20:37
Dan Grossman
This should put you on the right track:

Code mysql:

```DROP PROCEDURE IF EXISTS `pricing`; DELIMITER \$\$   CREATE PROCEDURE `pricing`() BEGIN   DECLARE f VARCHAR(255); DECLARE no_more_formulas INT DEFAULT 0;   DECLARE formula_csr CURSOR FOR SELECT formula FROM formulas; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_formulas = 1;   OPEN formula_csr; REPEAT FETCH formula_csr INTO f; SET @sql = CONCAT("INSERT INTO results (result) VALUES (", f, ")"); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; UNTIL no_more_formulas END REPEAT;   CLOSE formula_csr;   END\$\$ DELIMITER ;```

Code:

```mysql> select * from formulas; +-------------+ | formula    | +-------------+ | 20.00+20.00 | | 100.00*2.00 | +-------------+ 2 rows in set (0.03 sec) mysql> call pricing; Query OK, 0 rows affected (0.07 sec) mysql> select * from results; +--------+ | result | +--------+ |    40 | |    200 | |    200 | +--------+ 3 rows in set (0.03 sec)```
Dunno what's up with the duplicate, but my bugs are beside the point, that you can "eval" a stored query like this.
• Dec 1, 2009, 21:55
oddz
Can something "like" this be done? I seem to be receiving an error stating that the function EXEC doesn't exists? Is EXEC not supported in functions?

Code SQL:

```CREATE FUNCTION apply_string_pricing_calc (price DECIMAL(20,2),equation VARCHAR(96)) RETURNS DECIMAL(20,2) RETURN EXEC(REPLACE(equation,'?',price));```

Appreciate the help.
• Dec 1, 2009, 22:39
oddz
Code SQL:

```DELIMITER // CREATE PROCEDURE apply_string_pricing_calc (IN price DECIMAL(20,2),IN equation VARCHAR(96)) BEGIN SET @SQL := CONCAT('SELECT ',REPLACE(equation,'?',price)); PREPARE s FROM @SQL; EXECUTE s; END// DELIMITER ;```

Code SQL:

`CALL apply_string_pricing_calc('40.00','?*4');`

Produces: 160.00 which is perfect. Now just how to figure out to use it inside a query…