How can I optimize CASE WHEN - THEN statements when the conditional expression and the assigned expr-CodePudding

I have a long nested SELECT query with

CASE WHEN complex_function (field) > x 
THEN complex_function (field) 
ELSE z END.

My understanding is that complex_function in the above syntax will be executed twice, once when checking for the condition, and then when actually assigning values. Is there any way to have it calculated only once to speed up execution, or will it be taken care of by the SQL engine under the hood.

This is in a subquery within several layers of nested SELECT statements. In non-SQL, I would just assign a variable to the complex function output and reuse it.

CodePudding user response：

You could further nest the calculation in another level of the query:

SELECT CASE WHEN complex > x  THEN complex ELSE z END
FROM   (SELECT complex_function (field) 
        FROM   some_table -- or complicated nested query
       ) t

CodePudding user response：

You can use a CTE, to add the calculated field in the table where you have the field parameter and then use this table in your JOIN instead of original table

;WITH
C AS (
    SELECT *, complex_function(field) calc_field
    FROM table_containing_the_field
)
SELECT *, CASE WHEN calc_field > x  THEN calc_field ELSE z END
FROM your_table T 
JOIN /* table_containing_the_field */ C ON T.some_field = C.some_field