vendredi 1 juillet 2016

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!

Aucun commentaire:

Enregistrer un commentaire