I thought about using a user variable to store the results of the phase_1 calculation. That would have changed the query to be:
SELECT @p1 := 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 @p1 + out_value WHEN out_method = 'Subtract' THEN @p1 - out_value WHEN out_method = 'Multiply' THEN @p1 * out_value WHEN out_method = 'Divide' THEN @p1 / out_value END as phase_2 FROM filter Then I continued reading http://dev.mysql.com/doc/mysql/en/Variables.html and figured out that each phase_2 calculation will be computed using the phase_1 result from the PREVIOUS row (as the value is not committed to the variable until the end of each row's processing). So, this method won't work either. You may have to actually do this in two steps using either a temporary table or a subquery to compute the phase_1 results. Here's a temp table method: CREATE TEMPORARY TABLE tmpPhase1 SELECT ID, 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, FROM filter; SELECT f.id, tp.phase_1, CASE WHEN out_method = 'Add' THEN phase_1 + out_value WHEN out_method = 'Subtract' THEN phase_1 - out_value WHEN out_method = 'Multiply' THEN phase_1 * out_value WHEN out_method = 'Divide' THEN phase_1 / out_value END as phase_2 from filter f inner join tmpPhase1 tp on tp.id = f.id; DROP TEMPORARY TABLE tmpPhase1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sorry. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Eamon Daly" <[EMAIL PROTECTED]> wrote on 10/25/2004 05:41:25 PM: > 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] >
