Is possible to reuse a already calculatesd column into a query in MySQL?

Question

I have a query with complicated column, and I would like to use this column result in another column, for instance:

SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from table;

but his query returns an error:

Error Code: 1054. Unknown column 'myValue' in 'field list

How can I reuse a already calculated field?

Answer 1

You can use user-defined variable.

SELECT @temp := ( /* Complex query */ ) AS myValue, 
       IF ( @temp > 10, "OK" , "" )
FROM table;

Maybe server demands preliminary variable definition. If so,

SELECT @temp := ( /* Complex query */ ) AS myValue, 
       IF ( @temp > 10, "OK" , "" )
FROM table, (SELECT @temp := 0) dummy;

The documentation do not specified the order of output values calculations, but in practice it always matched the order they are written.

Answer 2

I just learned that this is valid in MySQL (I am using version 8.0.21).

SELECT 1 AS X, 2 AS Y, (SELECT X + Y) AS Z

Therefore, you can try

SELECT /*Complex query*/ as myValue, 
(SELECT IF(myValue > 10, 'OK', '')) AS OK_if_higher_than_ten
FROM table;

Answer 3

My preference here is to use a sub-query, summarizing the results on the outside query:

    SELECT sq.myvalue, IF(sq.myvalue > 10 , "OK" , "") as `myresult`
    FROM ( 
        SELECT ( /* complex query */ ) as `myvalue` 
        FROM table
    ) sq;

An alternate approach repeats the complex query within the IF () statement rather than use myvalue

    SELECT 
        ( /*complex query*/ ) as `myvalue`
        , IF ( ( /*complex query*/ ) > 10 , "OK", "" ) as `myresult`
    FROM table;

Don't really care for that approach. The more complex the query (or calculation) the less desirable this alternative becomes over time.