filter based on variable, if no value in variable apply NO filters

Question

Based on a filter I would like to apply that to what to filter.

For example if I have a variable called p_filter that could have the values 'A' or 'B' I would like to filter specific where clauses based on that.

SELECT *
FROM t1
WHERE
id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) --only apply this filter if p_filter = 'A'
AND id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )  --only apply this filter if p_filter = 'B'

If the variable though is null, I would like for it to apply NO filters. I tried something like this but it does not work:

SELECT *
FROM t1
WHERE
CASE WHEN :p_filter != 'A' THEN 1 WHEN id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) THEN 1 ELSE 0 END = 1
AND CASE WHEN :p_filter != 'B' THEN 1 WHEN  id NOT IN (select id from t3 WHERE t1.id = t3.id(+) ) THEN 1 ELSE 0 END = 1;

UPDATE: I am so stupid, I meant if null apply no filters!

krokodilko · Accepted Answer · 2016-06-24 21:16:32Z

1

Just do:

WHERE
   --only apply this filter if p_filter = 'A'
   p_filter = 'A' 
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 

   OR

   --only apply this filter if p_filter = 'B'
   p_filter = 'B' 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )  

   OR

   -- If the variable though is null, I would like for it to apply both filters
   p_filter IS NULL
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )

---- EDIT ---------

I am so sorry, I am stupid, I meant to say if null apply NO filters! – user2924127

In that case just remove the last condition, and add OR p_filter IS NULL:

WHERE
   --only apply this filter if p_filter = 'A'
   p_filter = 'A' 
   AND
   id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ) 

   OR

   --only apply this filter if p_filter = 'B'
   p_filter = 'B' 
   AND
   id NOT IN (select id from t3 WHERE t1.id = t3.id(+) )  

   -- I am so sorry, I am stupid, I meant to say if null apply NO filters!
   OR
   p_filter IS NULL

edited Jun 24, 2016 at 21:16

answered Jun 24, 2016 at 20:33

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

1 Comment

user2924127 Over a year ago

I am so sorry, I am stupid, I meant to say if null apply NO filters!

Paul X · Accepted Answer · 2016-06-24 23:10:31Z

1

To do this "OR" the negative of your condition like so:

SELECT *
FROM t1
WHERE 
(:p_filter IS NULL OR :p_filter!='A' OR id NOT IN (select id from t2 WHERE t1.id = t2.id(+) ))
AND (:p_filter IS NULL OR :p_filter!='B' OR id NOT IN (select id from t3 WHERE t1.id = t3.id(+) ))

edited Jun 24, 2016 at 23:10

answered Jun 24, 2016 at 20:37

Paul X

3891 gold badge3 silver badges10 bronze badges

2 Comments

user2924127 Over a year ago

I am so sorry, I am stupid, I meant to say if null apply NO filters!

Paul X Over a year ago

I adjusted the query above to make null do neither filter.

Анатолий Предеин · Accepted Answer · 2016-06-29 12:22:47Z

0

SELECT *
  FROM t1
 WHERE ( 
         (p_filter = 'A' and id NOT IN (select id from t2 WHERE t1.id = t2.id(+) )) 
          or 
         (p_filter = 'B' and id NOT IN (select id from t3 WHERE t1.id = t3.id(+) ))
          or 
         (p_filter is null)
       )

answered Jun 29, 2016 at 12:22

Анатолий Предеин

5715 silver badges9 bronze badges

1 Comment

Gennadii Saprykin Over a year ago

Please add some explanation to your answer. Code-only answers are discouraged.

Collectives™ on Stack Overflow

filter based on variable, if no value in variable apply NO filters

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related