CODEWITHSUNDEEP
sqlinformixnvl
user1971376
user1971376

Reputation: 93

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

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

Upvotes: 2

Views: 15206

Answers (3)

Nabheet
Nabheet

Reputation: 1314

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.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 754700

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.

Upvotes: 0

woemler
woemler

Reputation: 7169

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

Upvotes: 2

Related Questions