4

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
11

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 '19 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 '19 at 5:40
  • 1
    I signed up here just to give you my +1. THANKS!!!
    – user151851
    Jul 14 '20 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 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 at 9:18
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, privacy policy and cookie policy

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