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

I'm having a hard time following, but isn't it as simple as changing `a.to_date > current` to `NVL(a.to_date, '9999-12-31 23:59:59') > current`? Also, you really should be using [ANSI-standard joins](http://en.wikipedia.org/wiki/Join_(SQL)), and not `a.body_id = b.body_id`. — LittleBobbyTables - Au Revoir, Feb 26 '13 at 17:47
@LittleBobbyTables Could also use the ANSI-standard function `COALESCE()` instead of `NVL()`. — RBarryYoung, Feb 26 '13 at 17:53
@RBarryYoung: COALESCE is not available in Informix; NVL is. — Jonathan Leffler, Feb 26 '13 at 20:07
@JonathanLeffler Ah, thanks. The question did not originally specify, so I assumed that it was Oracle from the NVL(). — RBarryYoung, Feb 26 '13 at 20:34

Nabheet · Answer 1 · 2013-02-27T18:07:43.153

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.

edited Feb 27 '13 at 18:07

answered Feb 26 '13 at 17:56

Nabheet

1,285
1
12
22

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 Feb 26 '13 at 18:11
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`. – woemler Feb 26 '13 at 18:25
Awesome thanks for the clarification, that clears a lot of doubts – user1971376 Feb 26 '13 at 18:51

woemler · Answer 2 · 2013-02-26T18:20:55.130

2

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

edited Feb 26 '13 at 18:20

answered Feb 26 '13 at 17:57

woemler

7,089
7
48
67

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)` – ypercubeᵀᴹ Feb 26 '13 at 17:59
Does this method take less time to evaluate? I like my method because it makes it easier to read, IMO. – woemler Feb 26 '13 at 18:01
2

Applying a function to a column has (usually) the effect of making indexes unusable. So yes, this way you (may) get better performance. – ypercubeᵀᴹ Feb 26 '13 at 18:02
I made the required change as suggested before and got the following error: Non numeric character in datetime or interval – user1971376 Feb 26 '13 at 18:12
Sorry, sloppy answer on my part. The replacement date needs to be converted to a `DATETIME` value. Fixed my answer – woemler Feb 26 '13 at 18:22
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 Feb 26 '13 at 18:50
Gotcha, you should have tagged the question for Informix. I don't have any experience with that SQL implementation. – woemler Feb 26 '13 at 19:09

score 0 · Answer 3 · answered Feb 26 '13 at 20:16

The basic problem is that the 'display labels' or 'column aliases' are not available for use in the body of the query (the WHERE clause). You could get around this using a sub-query:

SELECT id, title, body, to_date, cr_date
  FROM (SELECT a.id,
               a.title,
               b.body,
               NVL(a.to_date, '9999-12-31 23:59:59') AS to_date,
               b.cr_date
          FROM a JOIN b ON a.body_id = b.body_id
         WHERE a.cust_id = 20
           AND a.from_date <= CURRENT
       ) AS t
 WHERE to_date > CURRENT

This avoids repeating the NVL expression.

Note that you had a conflict between todate in an AS and to_date in the WHERE. I also recommend identifying which table each column comes from when a query uses more than one table (as the inner query does, but the outer query does not). I usually use single-letter aliases for the tables; this time, the aliases were unnecessary since the table names given were single-letter names.

The query plan for this is likely to be the same as for the single-level query. You could check using SET EXPLAIN, of course.

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

3 Answers3