user2924127 user2924127 - 5 months ago 7
SQL Question

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

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!

Answer

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
Comments