CODEWITHSUNDEEP
sqloracle-database
Matt
Matt

Reputation: 15061

Oracle SQL CASE expression in WHERE clause only when conditions are met

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

Answers (3)

Florin Ghita
Florin Ghita

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

Emil Moise
Emil Moise

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

Gordon Linoff
Gordon Linoff

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

Related Questions