'Filtering column using input parameter

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.

sqloracleparametersnullcase


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ARC

Related Questions