0

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?

  • Your CASE statement doesn't include any other WHEN options for values other than "Day Start", so all the others will return as NULL. Either add another WHEN to handle the other values or use your WHERE clause to only return "Day Start" values if that is your goal. – LowlyDBA Jun 7 '16 at 13:59
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;
0

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.

0

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?

-1
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;
  • This is needlessly complex. The second part of the WHERE clause could just be Log = 'Day Start'. Note to asker: This will return one row (with a as "Running") if there are any rows that match the WHERE clause, and 0 rows if there aren't; It's not clear if that was the intent. – RDFozz Aug 10 '18 at 15:15

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.