0

I have a query using a NVL on the where statement as follows:

CREATE TABLE fruits (
    fruit_name VARCHAR2(100),
    color VARCHAR2(100)
)

INSERT ALL 
    INTO fruits(fruit_name, color) 
    VALUES ('Apple','Red') 
    INTO fruits(fruit_name, color)  
    VALUES ('Orange','Orange') 
    INTO fruits(fruit_name, color)
    VALUES ('Banana','Yellow')
    INTO fruits(fruit_name, color)
    VALUES ('Cherry','')
    INTO fruits(fruit_name, color)
    VALUES ('','Purple')
SELECT 1 FROM dual;

Now, issue lies on the - real - possibility of having a null value on the NVL function, like this:

select distinct
*
from fruits
where 
fruit_name = nvl(:MY_VARIABLE, fruit_name)

When MY_VARIABLE is null the result would be:

FRUIT_NAME  COLOR
Apple       Red
Orange      Orange
Banana      Yellow
Cherry      -

Row with the color 'Purple' was not listed here. Is there any workaround to avoid this and get the 'Purple' row on the results as well? (returning all values)

Yes, No Value Found will replace and use the specified column when null is found. But is there a way to use it (or any other) that allows to bring all the values including null?

Thanks

MT0
  • 143,790
  • 11
  • 59
  • 117
JB999
  • 441
  • 3
  • 16
  • 3
    In Oracle empty string is the same as `null`. Use `column = :param or :param is null` – astentx May 27 '23 at 18:59
  • Such a simple but clever solution, thanks so much. It worked – JB999 May 27 '23 at 19:20
  • 2
    As per @astentx comment, see the second answer on the linked duplicate (which was also a duplicate of your previous question). – MT0 May 27 '23 at 20:07
  • 1
    And note that accepted or most upvoted answer in duplicate (or any other question) is sometimes not the best. It may be outdated, may have alternative solutions that may better fit your need but not the author of linked question – astentx May 27 '23 at 20:38
  • 1
    There could be an optimisation aspect to this if you have an index on fruit_name. If so, you might consider an index on the expression `nvl(fruit_name,'?')` and then query using `nvl(fruit_name,'?') = nvl(:my_variable, nvl(fruit_name,'?'))`. To make this more manageable, you might instead create a virtual column as `nvl(fruit_name,'?')` and index that column. – William Robertson May 28 '23 at 12:32

0 Answers0