Columns defined through if statement

Question

When I use an if statement to get a result like this:

SELECT 
IF(huxwz_user_orders.year = YEAR(CURDATE()) + 1, huxwz_user_orders.plannedweek + 52, huxwz_user_orders.plannedweek) as PlannedWeekG

When I used PlannedWeekG in my query like this: WHERE PlannedWeekG > 0

It's giving me an error "Unknown column 'PlannedWeekG' in where clause. What am I doing wrong?

score 1 · Accepted Answer · answered Oct 18 '13 at 22:29

1

You need to use 'HAVING' in place of 'WHERE' to look at the derived result. Since PlannedWeekG is not a column in the DB, 'HAVING' is the magic to making this work ;)

answered Oct 18 '13 at 22:29

Joe T

2,300
1
19
31

score 0 · Answer 2 · answered Oct 18 '13 at 22:29

0

You can use the Having clause in mysql:

SELECT 
IF(huxwz_user_orders.year = YEAR(CURDATE()) + 1, huxwz_user_orders.plannedweek + 52, huxwz_user_orders.plannedweek) as PlannedWeekG
HAVING PlannedWeekG > 0

answered Oct 18 '13 at 22:29

Ibu

42,752
13
76
103

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

You can not use the computed column PlannedWeekG on the WHERE clause since it still does not exist when the WHERE clause is processed. You can use the HAVING clause as suggested in other answers or use your query as a derived table on another query that selects from the derived table.

Please look at this answer, especially the part on Logical Processing Order of the SELECT statement.

edited May 23 '17 at 11:50

Community

1
1

answered Oct 18 '13 at 22:37

Xint0

5,221
2
27
29

Columns defined through if statement

3 Answers3