SQL substituting NULL using NVL, having problems checking substituted value in the where clause correctly

Question

I am running a query to display messages when the from date is before (<=) the current date and to_date is after (>) the current date.

If the to_date is NULL, then i give it a value using the NVL 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.

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 <= current and 
       a.to_date > current

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.

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.

Can you please help me with this issue. Thanks

Answer 1

You need to read up on NULL value comparison - http://www.w3schools.com/sql/sql_null_values.asp

NULL and 'SOME_DATE' cannot be compared with =, <, or <> operators. The comparison with these operators will always be false.

So, your condition "a.to_date > current" will always be false, and any rows with a NULL to_date will never be returned.

If you need them returned you need to change your query:

where  a.cust_id = 20 and
       a.body_id = b.body_id and
       a.from_date <= current and 
       ((a.to_date > current) OR (a.to_date IS NULL))

Hope this helps.

EDIT:

Based on your comments I wanted to clarify that there is an order of precedence in a SQL execution. In this case, the FROM clause will get evaluated first, then the WHERE and finally the SELECT clause. Anything you do in the SELECT clause only affects how the output is "displayed". It does not change the number of rows evaluated. In your example, the where clause has already eliminated the NULL values, so the NVL() replacement in the SELECT clause is actually redundant because there will never be any NULL values in the output.