Filtering column using input parameter-CodePudding

I need to filter a column based on an outside parameter's value, but I do not know how to handle the NULL case since it requires a different syntax. What I have is something like this:

WHERE c.fruit LIKE
(CASE :fruitParam
    WHEN 'Orange' THEN 'OR'
    WHEN 'Apple' THEN 'APL'
    WHEN 'Any' THEN '%'
    WHEN 'None' THEN NULL  -- doesn't work since we can't do: LIKE NULL
END)

In this instance, there are rows that need to be returned where c.fruit is null. Is there an alternative way to write this such that when the parameter 'fruitParam' is 'None' we can set the condition: c.fruit IS NULL?

This is using Oracle.

CodePudding user response：

You could just move it into another clause:

WHERE (c.fruit LIKE
CASE :fruitParam
    WHEN 'Orange' THEN 'OR'
    WHEN 'Apple' THEN 'APL'
    WHEN 'Any' THEN '%'
END)
or (:fruitParam = 'None' and c.fruit is null)