Case statement in where clause with "not equal" condition
This is just a very simple version of my actual query and I'd like to know how I can write just one query instead of two for the following select?
If @ShowZero = 0 Select Value From Metrics Else Select Value From Metrics Where Value <> 0
I have tried something as
Select Value From Metrics Where Value = Case @ShowZero = 0 then Value Else (here I'm stuck).
@ShowZero is a flag of 0 and 1.
You don't need a case for this:
Select Value From Metrics Where @ShowZero = 0 or Value <> 0;
This works, because, uhh, that is how boolean logic works. In other words, if you did a logic table for your expression, it would look like:
@ShowZero Value Action 0 0 Show row 0 other Show row other 0 Filter out row other other Show row
There are multiple ways to express this. The if statement is one of them. You could say: "I'm going to show a row unless @ShowZero is not 0 and value is 0". The where clause would be:
where not (@ShowZero <> 0 and Value = 0)
Alternatively, you could say: "I'm going to show a row when either of the following is true:
- @ShowZero = 0
- Value <> 0
If both are true great! I'll still show the row." This is the where clause that I used.