Re: SQL Question re: AND OR with nulls

Mark

Where is your closing brace in the first example ?

   and (date_termination is null OR date_termination >= to_date('01-jun-2002','dd-mon-yyyy')

If you've just done a typo in the post (and not in the code) then it sounds like you've possibly found a bug. Check on metalink for this as if it's a genuine bug I guess you won't be the first to have found it. There would likely be a patch for this kind of problem, 8i being as mature as it is.

Positioning shouldn't make a difference as boolean OR logic is commutative i.e. x OR y == y OR X.

Andy

<mark_at_markme.net> wrote in message news:3daccfdc.1733765_at_news.efn.org...
> 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 - 03:27:34 CDT