Okay, I'm at my wit's end on this one. Suppose I have a (grossly simplified) table like so:
mysql> select * from filter; +----+------+-------+-----------+----------+------------+-----------+ | id | name | value | in_method | in_value | out_method | out_value | +----+------+-------+-----------+----------+------------+-----------+ | 1 | foo | 1 | Multiply | 1 | Add | 2 | | 2 | foo | 2 | Divide | 3 | Subtract | 4 | | 3 | foo | 3 | Add | 5 | Multiply | 6 | | 4 | bar | 4 | Subtract | 7 | Divide | 8 | | 5 | bar | 5 | Multiply | 9 | Add | 10 | +----+------+-------+-----------+----------+------------+-----------+
where in_method and out_method are enums. I want to perform a transformation on the initial value using in_method, then perform another calculation of that result using out_method, like so:
+----+------+---------+---------+ | id | name | phase_1 | phase_2 | +----+------+---------+---------+ | 1 | foo | 1 | 3 | | 2 | foo | .67 | -3.33 | | 3 | foo | 8 | 48 | | 4 | bar | -3 | -0.38 | | 5 | bar | 45 | 55 | +----+------+---------+---------+
On paper, it's easy:
SELECT CASE WHEN in_method = 'Add' THEN value + in_value WHEN in_method = 'Subtract' THEN value - in_value WHEN in_method = 'Multiply' THEN value * in_value WHEN in_method = 'Divide' THEN value / in_value END as phase_1, CASE WHEN out_method = 'Add' THEN phase_1 + in_value WHEN out_method = 'Subtract' THEN phase_1 - in_value WHEN out_method = 'Multiply' THEN phase_1 * in_value WHEN out_method = 'Divide' THEN phase_1 / in_value END as phase_2 FROM filter
But, of course, that results in "ERROR 1054: Unknown column 'phase_1' in 'field list'". Do I need to create a temporary table just to hold all the phase_1 values? Ultimately, I want to group by name, so that seems like an awfully wasteful step. Am I missing something?
____________________________________________________________ Eamon Daly
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
