jeudi 26 mars 2015

Retrieve arithmetic from column and use it to calculate a value

Assume the following data:



TABLE articles
id price
-----------------
1 100
2 200

TABLE conditions
id modifier
-----------------
EXW *1
FCA *1.5
OT1 +125
OT2 *1.5+100


What I would like returned is this:



id price condition
----------------------------------
1 100 EXW
2 200 EXW
1 150 FCA
2 300 FCA
1 225 OT1
2 325 OT1
1 250 OT2
2 400 OT2


Basically, the code should take the price and the modifier (which is basically an incomplete formula) and execute it. The formula would likely require a slightly different format (perhaps price*1.5 or for more complex variations (price + 100) * 1.5


The alternative solution using only a multiplier and addition would be fairly simple, but things could get messy if division or multiple operations would be required (addition and then multiplication of that - see above example).


I am using SQLite 3.8.8.2 but I would be interested in implementations in other databases.


Aucun commentaire:

Enregistrer un commentaire