Reputation: 15061
Cant quite figure this one out, i have a set of conditions that i want to be met only if a value is in a field.
So if the Status is complete i want to have three where clause's, if the status doesn't equal complete then i don't want any where clause.
Code
SELECT *
FROM mytable
WHERE CASE WHEN Status = 'Complete'
THEN (included = 0 OR excluded = 0 OR number IS NOT NULL)
ELSE *Do nothing*
Upvotes: 2
Views: 355
Reputation: 17643
You can translate the natural language in SQL, then, if possible, reformulate.
SELECT *
FROM mytable
WHERE (Status = 'Complete' and (included = 0 OR excluded = 0 OR number IS NOT NULL))
or status <> 'Complete'
or status IS NULL;
Upvotes: 3
Reputation: 393
It doesn't look like you really need a CASE statement, just use it like this:
SELECT *
FROM mytable
WHERE where (Status = 'Complete' and (included = 0 OR excluded = 0 OR number IS NOT NULL)) or (*Your do nothing*)
Upvotes: 1
Reputation: 1271241
It is usually simple to only use boolean expressions in the WHERE
. So:
WHERE (Status <> 'Complete') OR
(included = 0 OR excluded = 0 OR number IS NOT NULL)
If Status
could be NULL
:
WHERE (Status <> 'Complete' OR Status IS NULL) OR
(included = 0 OR excluded = 0 OR number IS NOT NULL)
Upvotes: 4