1

I found this post: Using an Alias in SQL Calculations which suggests you can use an alias in calculations by using 

(select alias)

like:

SELECT 10 AS my_num, 
       (SELECT my_num) * 5 AS another_number
FROM table

This works fine. But now I want to use an alias in an if. So I thought it might work the same way. So I tried:

SELECT 10 AS my_num, 
       IFNULL(otherfield, (SELECT my_num)) AS another_number
FROM table

which doesn't work at all, telling me 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '(SELECT my_num)) AS another_number

Is there any way to make this work in MySql?

Community
  • 1
  • 1
Werner
  • 1,695
  • 3
  • 21
  • 42

1 Answers1

0

No, use the entire expression directly like below unless you are accessing it in a outer query.

IFNULL(otherfield, 10) AS another_number

In your case it should be

SELECT 10 AS my_num, 
       IFNULL(otherfield, 10) AS another_number
FROM table
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • The problem is that my_num is of course a much more complicated calculation depending on many IFs itself. The select would be much easier to maintain if it would be possible to just re-use the alias. – Werner Apr 21 '17 at 12:54