SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Threaded View
-
Dec 1, 2009, 21:00 #1
- Join Date
- Jul 2006
- Location
- Augusta, Georgia, United States
- Posts
- 4,194
- Mentioned
- 17 Post(s)
- Tagged
- 5 Thread(s)
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;
Bookmarks