T-SQL | Conditions in Where Clause

Question

I've a parameter that is a integer -> @Flag and I've a table with this structure:

ID     Date    Value_A  Value_B Value_C
A   2017-07-14  1          1       1
A   2017-07-13  1          0       1
A   2017-07-12  1          0       1

What I'm trying to get is: - If the parameter @Flag is > 0 then get the rows where Value_A, Value_B or Value_C don't have the same values. In the example above, if the @Flag is 1 then it returns second and third row. - If the parameter @Flag is = 0 then returns all the rows.

I'm trying with this script but I'm getting errors in Sub-Queries:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

DECLARE @Flag INT
SET @Flag = NULL

SELECT *
FROM teste
WHERE Value_A = CASE  
                  WHEN @Flag > 0 THEN 
                   (SELECT Value_B FROM teste WHERE Value_A <> Value_B or Value_A <> Value_C) 
                  ELSE (SELECT Value_A FROM teste) 
                END

How can I do this?

Thanks!

This answer shows how to use a CASE expression in a JOIN or WHERE. Andy's answer is still a clearer way to express your intent. — HABO
– HABO, Commented Jul 14, 2017 at 11:38

Andrey Korneyev · Accepted Answer · 2017-07-14 11:28:50Z

2

You can't compare single value with set as Value_A = (some set of values).

But in your particular case it is not needed at all, since your query can be simplified as:

SELECT *
FROM teste
where
    (@Flag > 0 and ( Value_A <> Value_B or Value_A <> Value_C))
    or @Flag = 0

answered Jul 14, 2017 at 11:28

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-07-14 12:16:22Z

0

Assuming the values are not the NULL, and you only care about flag being 0 or not zero:

SELECT *
FROM teste
WHERE @Flag = 0 OR
      (Value_A <> Value_B OR Value_A <> Value_C)

answered Jul 14, 2017 at 12:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

T-SQL | Conditions in Where Clause

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related