Selecting data in MySQL and limiting (WHERE) by temporary column data

Question

I am trying to sort order-entries by their status, however in my database there is no column for status and I'm trying to generate it on-the-fly with current values on other columns in the database.

I've created a "temporary" column in my query with a CASE-statement. What I want to do now is get only the table rows that match the value of the CASE-column.

Here's an example of what I've done so far:

CASE
  WHEN date_due = 0 AND date_sent = 0 THEN "opened" 
  WHEN date_sent > 0 AND (CURRENT_DATE - date_due) <= 0 THEN "sent" 
END AS status 

My problem is that status is now apparently an unknown column when i try to filter it:

... WHERE status = "sent" ...

What's the best solution in my case? Is it possible to define a temporary variable containing the status?

Answer 1

where is evaluated before the columns are. Therefore, when where is evaluated, there doesn't exist a status column. You will have to put your case statement or conditions in the where clause:

where
    CASE
      WHEN date_due = 0 AND date_sent = 0 THEN "opened" 
      WHEN date_sent > 0 AND (CURRENT_DATE - date_due) <= 0 THEN "sent" 
    END = 'sent'

More pragmatically, though, you'll want to do this:

where
    date_sent > 0
    and (CURRENT_DATE - date_due) <= 0

The only clause that can use aliased column names is the order by clause (across databases--some DB's, like MySQL, allow you to use them in group by).

Answer 2

In MySql, this works

select
...
CASE
  WHEN date_due = 0 AND date_sent = 0 THEN "opened" 
  WHEN date_sent > 0 AND (CURRENT_DATE - date_due) <= 0 THEN "sent" 
END AS status 

...
from table

where 
... (other conditions) ...

**HAVING status = "sent"**

If you dont need to include other conditions remove the WHERE, leaving just SELECT ... FROM ... HAVING ...