erzoolander
asked on
MySQL "case as column" question in WHERE clause
I'm setting up a new column with this:
So - numfield is created by the CASE clause...but in the WHERE clause it's telling me that numfield is an unknown column.
Do I have to declare the column or something for it to be recognized - and what would the syntax for that be?
Thanks in advance!
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'
AND numfield >= 3
So - numfield is created by the CASE clause...but in the WHERE clause it's telling me that numfield is an unknown column.
Do I have to declare the column or something for it to be recognized - and what would the syntax for that be?
Thanks in advance!
ASKER
In your query of
QUERY:
SELECT * ,
CASE thing
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 my_table
ORDER BY numfield DESC
Does it also work if you say
FROM my_table
WHERE numfield > 2
because that's where it's hanging up on me - weirdly
QUERY:
SELECT * ,
CASE thing
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 my_table
ORDER BY numfield DESC
Does it also work if you say
FROM my_table
WHERE numfield > 2
because that's where it's hanging up on me - weirdly
Hmm, no - the WHERE clause is sick. Seems like a good argument for using ALTER TABLE and putting the numeric value into a column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please see: http://iconoun.com/demo/temp_erzoolander.php
Open in new window