In article <[EMAIL PROTECTED]>,
"Eamon Daly" <[EMAIL PROTECTED]> writes:
> 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?
If you have only four different methods, you could use method
combinations in a single CASE statement:
SELECT CASE
WHEN in_method = 'Add' AND out_method = 'Add'
THEN value + in_value + out_value
WHEN in_method = 'Add' AND out_method = 'Subtract'
THEN value + in_value - out_value
...
END
FROM filter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
