Is there a way I can make the CASE WHEN test one of the results rather than running it twice?

Question

Is there a way to use the value from the CASE WHEN test as one of its results without writing out the select statement twice (since it could be long and messy)? For example:

 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

Is there a way I can use the result of the first run of the SELECT statement (for the test) as the THEN value as well? I tried using "AS max_value" after the first SELECT but it gave me a SQL error.

Update: Whoops, as Tom H. pointed out, I forgot the "IS NOT NULL" in my original question.

Answer 1

This example shows how you can prepare a bunch of values in a subquery, and use them in a CASE in the 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 that's what you're after, the question is unclear (for example, your 2 MAX() queries appear to be exact copies.)