SQL substituting NULL using NVL, having problems checking substituted value in the where clause correctly
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.
user1971376
Updated on June 04, 2022Comments
-
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
isNULL
, then i give it a value using theNVL
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ᵀᴹ about 11 yearsYes. 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 about 11 yearsDoes this method take less time to evaluate? I like my method because it makes it easier to read, IMO.
-
ypercubeᵀᴹ about 11 yearsApplying a function to a column has (usually) the effect of making indexes unusable. So yes, this way you (may) get better performance.
-
user1971376 about 11 yearsI 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 about 11 yearsI made the required change as suggested before and got the following error: Non numeric character in datetime or interval
-
woemler about 11 yearsSorry, sloppy answer on my part. The replacement date needs to be converted to a
DATETIME
value. Fixed my answer -
woemler about 11 yearsHaving
NVL(a.to_date, '9999-12-31 23:59:59') as todate
in theSELECT
clause does not alter the value ofto_date
. You still have to useNVL()
in theWHERE
clause to make the selectedto_date
value equivalent to the evaluatedto_date
. -
user1971376 about 11 yearsThanks. 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 about 11 yearsAwesome thanks for the clarification, that clears a lot of doubts
-
woemler about 11 yearsGotcha, you should have tagged the question for Informix. I don't have any experience with that SQL implementation.