# Thread: Applying Equation to Decimal Values In Query

1. ## 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;```

2. 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...

3. 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;```

4. 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.

5. 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.

6. 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…

#### Posting Permissions

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