Re: SQL Question re: AND OR with nulls

From: <kenneth.koenraadt_at_no-spam.hotmail.com>
Date: Wed, 16 Oct 2002 07:54:05 GMT
Message-ID: <3dad14b7.1118648@news.mobilixnet.dk>

Hi Mark,

Tried to recreate your error on both 8.1.7. and 9.2, and did not "succeed":

create table foo (date_termination date);

insert into foo values (sysdate + 100);
insert into foo values (sysdate + 10);
insert into foo values (sysdate - 1000);
insert into foo values (sysdate   -3000);
insert into foo values (null);
insert into foo values (null);

commit;

select nvl(to_char(date_termination),'NULL') from foo where (date_termination > to_date('2002-06-01','yyyy-mm-dd') or date_termination is null);
NVL(TO_C

---

03-01-24
02-10-26
NULL
NULL select nvl(to_char(date_termination),'NULL') from foo where (date_termination is null or date_termination > to_date('2002-jun-01','yyyy-mon-dd') );
NVL(TO_C

---

03-01-24
02-10-26
NULL
NULL Guess it is one of the other where clauses that is causing the error ?

• Kenneth Koenraadt

On Wed, 16 Oct 2002 02:33:28 GMT, mark_at_markme.net wrote:

>Hi all,
>
>I am running a simple select from SQL*Plus to create a view on an 8i
>database on NT. The last statement in the query reads as follows:
>
>and (date_termination is null OR date_termination >=
>to_date('01-jun-2002','dd-mon-yyyy')
>
>If I run it this way it returns the records with date_termination null
>but not the records with date_termination >= 01-jun-2002. If I reverse
>the ordering of the clauses around the OR as in
>
>and (date_termination >= to_date('01-jun-2002','dd-mon-yyyy') OR
>date_termination is null )
>
>then it returns both the records containing null term dates and the
>records with term dates >= 01-jun-2002. What I don't understand is why
>the change in position would change the result set if the whole
>statement is enclosed in parens. I know that AND binds more tightly
>than OR but the docs say the parens should overide this. Is it an an
>'unknown' value form the null that is causing the evaluation to
>terminate befoe the second clause is checked? I can make it do what I
>want but i don't understand why which is annoying.
>
>Thank you,
>mark
>
Received on Wed Oct 16 2002 - 02:54:05 CDT