CODEWITHSUNDEEP
sqloracle-database
Coding Duchess
Coding Duchess

Reputation: 6909

Adding CASE WHEN to WHERE clause

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)

Upvotes: 0

Views: 226

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

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)

Upvotes: 1

Related Questions