Query in MySQL

Question

MySQL

I have a query that calculates some value using CASE, for example:

SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column FROM table a;

How to use new_column value to calculate one more value? Those. something like:

SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column, (CASE WHEN new_column < 1000 THEN new_column ELSE new_column + 1000) as new_column2 FROM table a;

MySQL swears at such a request, says that Column not found: 1054 Unknown column 'new_column' in 'field list'

Answer 1

use full-length notation (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) instead of
new_column
into a character including register.

Answer 2

SELECT new_column, (CASE WHEN new_column < 1000 THEN new_column ELSE new_column + 1000 END) as new_column2 FROM (SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column FROM table as a) as b

Answer 3

-- Not an efficient way, but acceptable on small samples B
SELECT
  b.new_column,
  IF(B.new_column < 1000, B.new_column, B.new_column + 1000) AS new_column2
FROM(
  SELECT IF(a.id > 500, a.id, a.id + 500) as new_column
  FROM table a
) AS B;

- And it can be...
SELECT
  (a.id + IF(a.id < 500, 500, 0)) as new_column,
  (a.id + IF(a.id > 1000, 1000, 0)) as new_column2
FROM table a

Answer 4

Thanks for the answer. The fact is that the expression is quite long (5 lines), but since there is no other way out ...

Answer 5

Thanks to all!

Answer 6

Use ifs and variables instead of cases, this will reduce duplication, the first time you calculate everything and write to the variable, the second time use the variable instead of the entire expression