Optional parameter in where condition

Question

I have the following where condition:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy')

All works fine. But the second unnamed parameter is optional. How to check if it's empty or null then don't check the second condition?

Answer 1

Use CASE?

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

something like:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= (case when parameter2 is null then
sysdate
  else
to_date(parameter2, 'dd.mm.yyyy')

Answer 2

I think you should use dynamic SQL, snippet below:

command varchar2(500);
command := 'YOUR SQL';
command := command || ' lr."dateEnd" >= to_date(:1, ''dd.mm.yyyy'')';

if (secondParameter is not null) then
 command := command ||' '|| 'and lr."dateEnd" <= to_date(:2, ''dd.mm.yyyy'')' 
 execute immediate command using firstParameter, secondParameter;

else  
 execute immediate command using firstParameter;

end if;