title: Conditional where clause in Oracle SQL query
body: <p>I have a query in <code>APEX</code>, which uses a where clause condition based on a item's value in apex. If the date is <code>null</code> then I want to show all the records from the table, and if the date is not <code>null</code> i want to add where conditions based on that value. How can I do that ? </p>
  
  <p>I want to add below 2 conditions if the <code>P2_SELECT_DATE</code> is not null : </p>
  
  <pre><code>select * from HR_DATA
  where 
       :P2_SELECT_DATE between  person_eff_start_date and person_eff_end_date
   and :P2_SELECT_DATE between nvl(assign_eff_start_date,sysdate-1) and nvl(assign_eff_end_date, sysdate+1)
  </code></pre>
  
  <p>And if the <code>P2_SELECT_DATE</code> is null then I don't want to have these conditions in the where clause at all.</p>