7

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?

1

3 Answers 3

14

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.

6
  • Can you tell when and why can server demand preliminary variable definition?
    – Justinas
    Jun 14, 2019 at 5:35
  • @Justinas What variable? UDV and GUDV do NOT need a definition. But their initial value is ALWAYS NULL. So if you do not init they by assigning some value you must consider this fact.
    – Akina
    Jun 14, 2019 at 5:40
  • 2
    I signed up here just to give you my +1. THANKS!!!
    – user151851
    Jul 14, 2020 at 13:09
  • Very good way of doing this without wrapping your query having correlated subqueries with another layer of select. Big thanks! Sep 2, 2021 at 8:57
  • 1
    @İlterKağanÖcal I have said that the output expressions evaluation matches their order in the query text. It seems to be the same... Real important point is that the rowsource in outer query must be single table, not 2 or more joined tables.
    – Akina
    Sep 2, 2021 at 9:18
1

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

enter image description here

Therefore, you can try

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.