1

I'm setting up a new column with this case statement:

SELECT * , 
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1 
WHEN  'two' THEN 2 
WHEN  'three' THEN 3 
WHEN  'four' THEN 4 
WHEN  'five' THEN 5 
WHEN  'six' THEN 6 
WHEN  'seven' THEN 7 
WHEN  'eight' THEN 8 
WHEN  'nine' THEN 9 
WHEN  'ten' THEN 10 
ELSE 0 
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'

When I run that query - it works just fine - and I see numfield in the output.

However - when I append something to the WHERE clause...

AND numfield >= 3

It tells me that numfield is an unknown column.

Any ideas as to why? Is there a declaration I need to make somewhere? Thanks!

3

Aliases can't be used in WHERE. There are couple workarounds.
1. Repeat CASE in WHERE as oNare suggests
2. Put numfield >3 in HAVING instead of WHERE (works for Mysql only)
3. Rewrite query to use inline view syntax :

SELECT a.* FROM 
(
SELECT * , 
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1 
WHEN  'two' THEN 2 
WHEN  'three' THEN 3 
WHEN  'four' THEN 4 
WHEN  'five' THEN 5 
WHEN  'six' THEN 6 
WHEN  'seven' THEN 7 
WHEN  'eight' THEN 8 
WHEN  'nine' THEN 9 
WHEN  'ten' THEN 10 
ELSE 0 
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
)a 
WHERE  numfield >= 3
| improve this answer | |
  • 2
    Just a few suggestions. The workaround with HAVING instead of WHERE will work on SQL Server too. But I would suggest your solution too, as there isn't a code duplication and it's much better to read for everyone instead of repeating the code. But just one addition: The explanation why it doesn't work. The reason why it doesn't work using the WHERE is quite simple. The WHERE clause will be evaluated and executed before the SELECT. Due to this reason, WHERE can't know that you rename some expressions to a new column numfield. – Ionic Jun 24 '15 at 22:07
  • 1
    @Ionic: SQLServer (at least 2008R2 ) gives an error if HAVING is used without aggregate function/group by : "Msg 8121: [column] is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause." Or "Invalid column"... – a1ex07 Jun 24 '15 at 22:48
  • Yes but he can group it. So there isn't a problem? – Ionic Jun 25 '15 at 8:42
  • 1
    Suggestion for workaround #4: add WHERE (t2.field_max_occupancy_value NOT IN ('one', 'two') AND t2.field_max_occupancy_value IS NOT NULL) – ypercubeᵀᴹ Aug 15 '15 at 18:51
  • 1
    @Ionic: The HAVING workaround will not work in SQL Server. In Transact-SQL, you can reference a column alias only in ORDER BY. The usual workaround since SQL Server 2005 is to use APPLY.</offtopic> – Andriy M Aug 15 '15 at 19:32
2

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Try running:

SELECT 
    *, 
    CASE t2.field_max_occupancy_value
        WHEN  'one' THEN 1 
        WHEN  'two' THEN 2 
        WHEN  'three' THEN 3 
        WHEN  'four' THEN 4 
        WHEN  'five' THEN 5 
        WHEN  'six' THEN 6 
        WHEN  'seven' THEN 7 
        WHEN  'eight' THEN 8 
        WHEN  'nine' THEN 9 
        WHEN  'ten' THEN 10 
    ELSE 0 
        END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11' && 
    (CASE t2.field_max_occupancy_value
        WHEN  'one' THEN 1 
        WHEN  'two' THEN 2 
        WHEN  'three' THEN 3 
        WHEN  'four' THEN 4 
        WHEN  'five' THEN 5 
        WHEN  'six' THEN 6 
        WHEN  'seven' THEN 7 
        WHEN  'eight' THEN 8 
        WHEN  'nine' THEN 9 
        WHEN  'ten' THEN 10 
        ELSE 0 
        END) >=3;
| improve this answer | |

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.