title: SQL substituting NULL using NVL, having problems checking substituted value in the where clause correctly
body: <p>I am running a query to display messages when the from date is before (&lt;=) the current date and to_date is after (>) the current date.</p>
  
  <p>If the <code>to_date</code> is <code>NULL</code>, then i give it a value using the <code>NVL</code> method and make the check n the where clause, and if it is not null - ie the user has provided the value for it - then it should use that value and check the condition in the where clause.</p>
  
  <pre><code>select a.id,
         title,
         body,
         NVL(a.to_date, '9999-12-31 23:59:59') as todate,
         cr_date
  from   a,
         b
  where  a.cust_id = 20 and
         a.body_id = b.body_id and
         a.from_date &lt;= current and 
         a.to_date &gt; current
  </code></pre>
  
  <p>However when I do run this query for my database, I only get the results for when the user has entered a to_date. I DO NOT get results when to_date is null and it has the value '9999-12-31 23:59:59' - i.e.: 
  It fails the a.to_date > current condition in the where clause and so does not return those entries. The value when to_date is NULL is substituted correctly with '9999-12 ...' but the where condition '> current' fails.</p>
  
  <p>I want to make the substitution within the query and not as a seperate statement. Logically I think it should work but for some reason I am going wrong somewhere . Also not sure if this effects the query execution but the default value of to_date is set to NULL when creating the table.</p>
  
  <p>Can you please help me with this issue. Thanks</p>