Is there a way that CASE WHEN can test one of the results rather than running it twice?
Is there a way to use the value from the CASE WHEN test as one of its results without having to write out the select statement twice (since it can be long and messy)? For instance:
SELECT id,
CASE WHEN (
(SELECT MAX(value) FROM my_table WHERE other_value = 1) IS NOT NULL
)
THEN (
SELECT (MAX(value) FROM my_table WHERE other_value = 1
)
ELSE 0
END AS max_value
FROM other_table
Can the result of the first run of a SELECT statement (for a test) be used as a THEN value? I tried using "AS max_value" after the first SELECT, but it gave me a SQL error.
Update : Oops, as Tom X pointed out, I forgot "NOT NULL" in my original question.
a source to share
This example shows how you can prepare a bunch of values in a subquery and use them in a CASE in an outer SELECT.
select
orderid,
case when maxprice is null then 0 else maxprice end as maxprice
from (
select
orderid = o.id,
maxprice = (select MAX(price) from orderlines ol
where ol.orderid = o.id)
from orders o
) sub
Not sure if the question is unclear after this (e.g. your 2 MAX () queries seem to be exact copies.)
a source to share
Is your expression being executed? It is not a boolean expression in your CASE statement. By default MySQL will check for NULL by default unless you use boolean?
I think this is probably what you are looking for:
SELECT
id,
COALESCE(
(
SELECT
MAX(value)
FROM
My_Table
WHERE
other_value = 1
), 0) AS max_value
FROM
Other_Table
a source to share