Oracle SQL Case with Null

June 1, 2023

I have a simple query that is taking an input from a bind variable.

CREATE TABLE "FRUITS" 
(   "FRUIT_NAME" VARCHAR2(100), 
"COLOR" VARCHAR2(100)
) ;

insert into fruits (fruit_name, color)
values ('Banana', 'Yellow')
insert into fruits (fruit_name, color)
values ('Lemon', '')
insert into fruits (fruit_name, color)
values ('Apple', 'Red')

SELECT * FROM FRUITS
WHERE
COLOR =     case
            when :P1_ITEM is null then null
            else :P1_ITEM
            end

If the input is ‘Yellow’ the result would be ‘Banana’ (when ‘Red’ then ‘Apple’). However, if the input happens to be null the result is ‘no data found’.
How can this be avoided knowing that null is not a null value?
If the input is null on color then how can I return the null color row? meaning ‘Lemon’ + null

Thanks

>Solution :

Something like this might be one option:

SELECT * FROM FRUITS
WHERE
nvl(COLOR, 'x') = case
                    when :P1_ITEM is null then 'x'
                    else :P1_ITEM
                  end;