0

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?

3

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.

  • 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
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.