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!
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire