Oracle SQL NVL return more than one value

November 9, 2023

I want to list active and inactive employee when pIsActive is null.

pIsActive = (0,1,null)

select e.*
from employee e
where e.id in (1,2,3,4,5)
and (e.isactiv = NVL(pIsActive, (0,1)));

>Solution :

NVL and COALESCE will only return a scalar value not a list of values.

Instead, use OR and IN:

SELECT e.*
FROM   employee e
WHERE  e.id IN (1,2,3,4,5)
AND    (  e.isactiv = pIsActive
       OR (pIsActive IS NULL AND e.isactiv IN (0,1))
       );

You could also write the query as:

SELECT e.*
FROM   employee e
WHERE  e.id IN (1,2,3,4,5)
AND    e.isactiv IN ( NVL(pIsActive, 0), NVL(pIsActive, 1) );

But I find the first option to be easier to understand and this second option I have to think about for a while to work out that you are effectively doing e.isactive = pIsActive if pIsActive IS NOT NULL and e.isactive IN (0,1) otherwise.