COLOR filter in CASE statements

My sample table looks as follows:

NameSalesTasks
Shawn100
Martha2000015
Stewart05
Angela00

I am trying to add a color filter to my case statements for better row identification

I want the first 3 rows to be in blue since they have some value in the ‘sales’ and ‘tasks’ field.
Whereas, for Angela, I want the row to be white in color.

CASE WHEN `Sales` IS NOT NULL AND `Tasks` IS NOT NULL
THEN '#B7DAF5' (light blue)
ELSE '#FFFFFF' (white)
END

It still shows every row as white.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

0 is not the same as NULL. Also, your condition is backwards, it will only make it light blue of ALL fields are not null; therefore, Shawn and Stewart would be white.

Simplify it by using a positive condition rather than negative.

CASE WHEN `Sales` = 0 AND `Tasks` = 0
THEN '#FFFFFF' -- white
ELSE '#B7DAF5' -- light blue
END

Method 2

In your sample table there are no NULLs in any of the rows, unless you are representing NULL with 0 (zero). Anyway, I think it would be necessary to know what client program you are using.

Method 3

The sample data you put contains 0 instead of NULL so you might want to check for is greater than 0 instead of IS NOT NULL.

select *,
CASE
    WHEN Sales > 0 or Tasks > 0 THEN "#B7DAF5"
    ELSE "#FFFFFF"
END as Color
from TableName


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x