CASE statement multiple conditions

Question

I've got a simple table below,

Select * from message_log where  Message = 'BUILD' order by 1;

+------------+-------------+
|  **Message |     Log**   |
+------------+-------------+
|    Build   |  Day Start  |
+------------+-------------+
|    Build   |  Day End    |
+------------+-------------+
|    Build   |  Dusk Start |
+------------+-------------+
|    Build   |  Dusk End   |
+------------+-------------+
|    Build   |  Night Start|
+------------+-------------+
|    Build   |  Night End  |
+------------+-------------+

If I for example write a CASE statement

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD' order by 1;

It'll return a Null value and a 'RUNNING' value, can I write this in such a way that it doesn't include the null row?

Answer 1

Just add a WHERE condition. And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value.

So here is the code to your original question:

SELECT 'RUNNING' AS A
from message_log 
where  Message = 'BUILD' 
    AND Log = 'Day Start'
order by 1;

And here is the code for a multi-condition CASE:

SELECT CASE 
    WHEN (Log = 'Day Start') THEN 'RUNNING' 
    WHEN (Log = 'Day End')   THEN 'NOT RUNNING'
    ELSE 'UNKNOWN' END AS A
from message_log 
    where  Message = 'BUILD' 
order by 1;

Answer 2

Rather than putting the data in a case statement where you might have to repeat that case statement in more than one query, you could create a table with static data, then joint to that table. The advantage would be that you can change the data in the table easier than changing all of the queries that have that case statement.

Answer 3

What is it that you are trying to achieve?

SELECT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log 
where Message = 'BUILD' 
order by 1;

... will scan every row, returning either "RUNNING" or NULL for each row. There could be millions of rows in this result set.

SELECT DISTINCT ... query 

... will take each and every one of those returned values and boil them down to just the [two] "distinct" values.

This doesn't strike me as an overly "useful" query. What did you hope it would do?

Answer 4

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD' 
AND 
(
CASE 
WHEN (Log = 'Day Start') THEN 'RUNNING' 
END)='RUNNING'
order by 1;