Adding CASE WHEN to WHERE clause

Question

I used the following WHERE clause

WHERE (TYPE1 = :P3_ITEM1 OR :P3_ITEM1 IS NULL)

to filter my query on TYPE1 and it worked fine to display the records of same TYPE1 when page item P3_ITEM1 is not null.

Now I need to add two more filters for TYPE2 and TYPE3. Normally only one page item P3_ITEM1, P3_ITEM2, or P3_ITEM3 is not null, so I need to filter on whichever on is not null.

I tried

WHERE (TYPE1=:P3_ITEM1 OR :P3_ITEM1 IS NULL) OR
  (TYPE2=:P3_ITEM2 OR :P3_ITEM2 IS NULL) OR
  (TYPE3=:P3_ITEM3 OR :P3_ITEM3 IS NULL)

but it did not work.

I tried using CASE statement in my WHERE clause but no success so far. Can anyone help?

WHERE CASE
WHEN (:P3_ITEM1 IS NOT NULL) THEN (TYPE1=:P3_ITEM1)
WHEN (:P3_ITEM2 IS NOT NULL) THEN (TYPE2=:P3_ITEM2)
WHEN (:P3_ITEM3 IS NOT NULL) THEN (TYPE3=:P3_ITEM3)

It's generally better to use `AND`/`OR` constructions instead of `case` expressions in the `WHERE` clause. — jarlh, Nov 01 '18 at 15:28
You can probably find answer here https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server — Amel, Nov 01 '18 at 15:33

score 1 · Accepted Answer · answered Nov 01 '18 at 15:33

1

You can simply use AND to combine the various filters. Each filter will evaluate to true if the parameter for it is NULL, so if all three are NULL you will get all records, but you can fill in a any, or even all three parameters to filter out unwanted records.

WHERE
  (TYPE1 = :P3_ITEM1 OR :P3_ITEM1 IS NULL) AND
  (TYPE2 = :P3_ITEM2 OR :P3_ITEM2 IS NULL) AND
  (TYPE3 = :P3_ITEM3 OR :P3_ITEM3 IS NULL)

answered Nov 01 '18 at 15:33

GolezTrol

109,399
12
170
196

It worked! I do not know why I thought changing OR to AND will not work! Thank you – Coding Duchess Nov 01 '18 at 15:39

Adding CASE WHEN to WHERE clause

1 Answers1