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

14,904

Solution 1

I'm not 100% clear on your question, but it sounds like you want your substitute date evaluated in the WHERE clause, even though you have not included the NVL(). Have you tried this:

select a.id,
   title,
   body,
   NVL(a.to_date, to_date('9999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) 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 
   NVL(a.to_date, to_date('9999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) > current

Solution 2

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.

Share:
14,904
user1971376
Author by

user1971376

Updated on June 04, 2022

Comments

  • user1971376
    user1971376 almost 2 years

    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

  • ypercubeᵀᴹ
    ypercubeᵀᴹ about 11 years
    Yes. Even better, change NVL(a.to_date, '9999-12-31 23:59:59') > current to (a.to_date > current OR a.to_date IS NULL)
  • woemler
    woemler about 11 years
    Does this method take less time to evaluate? I like my method because it makes it easier to read, IMO.
  • ypercubeᵀᴹ
    ypercubeᵀᴹ about 11 years
    Applying a function to a column has (usually) the effect of making indexes unusable. So yes, this way you (may) get better performance.
  • user1971376
    user1971376 about 11 years
    I was using NVL(a.to_date, '9999-12-31 23:59:59') as todate, in the select clause so to_date should never be null. It would either have 9999-12-31 23:59:59 given the select condition or a value specified by the user. So the comparison would in the where clause would be valid....I think? Am i correct in thinking so?
  • user1971376
    user1971376 about 11 years
    I made the required change as suggested before and got the following error: Non numeric character in datetime or interval
  • woemler
    woemler about 11 years
    Sorry, sloppy answer on my part. The replacement date needs to be converted to a DATETIME value. Fixed my answer
  • woemler
    woemler about 11 years
    Having NVL(a.to_date, '9999-12-31 23:59:59') as todate in the SELECT clause does not alter the value of to_date. You still have to use NVL() in the WHERE clause to make the selected to_date value equivalent to the evaluated to_date.
  • user1971376
    user1971376 about 11 years
    Thanks. Unfortunately I am using an informix database and the syntax for to_date is a bit different, but yes that will solve the issue.Much appreciated Thanks!
  • user1971376
    user1971376 about 11 years
    Awesome thanks for the clarification, that clears a lot of doubts
  • woemler
    woemler about 11 years
    Gotcha, you should have tagged the question for Informix. I don't have any experience with that SQL implementation.