CASE statement multiple conditions

by StevenM   Last Updated August 10, 2018 13:06 PM

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?

Tags : sql oracle case


Answers 3


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;
Steve Mangiameli
Steve Mangiameli
June 07, 2016 14:00 PM

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.

Gandolf989
Gandolf989
June 07, 2016 15:32 PM

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;

amit kumar
amit kumar
August 10, 2018 12:29 PM

Related Questions


Regular expression in a SQL case statement

Updated April 24, 2015 23:02 PM


how to write case when in where statement in oralce

Updated August 07, 2018 11:06 AM


Compare 2 dates using a CASE in WHERE

Updated September 14, 2016 09:02 AM