Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Question re: AND OR with nulls

Re: SQL Question re: AND OR with nulls

From: <mark_at_markme.net>
Date: Wed, 16 Oct 2002 14:26:08 GMT
Message-ID: <3dad73fd.913640@news.efn.org>


On Wed, 16 Oct 2002 09:27:34 +0100, "Andy" <andy.spaven_at_eps-hq.co.uk> wrote:

Thanks for the reply,

The ending brace was just the way i wrote the posting, the code itself has been run many times. I also tried to run the same code as a union with one of the conditions in each side of the union and that produces the same result set as placing the date_termnation null clause last which is what I would expect if the rest of the code logic were OK.

I should have added that I have been working against this particular schema for 4+ years and am very famililar with the tables and with writing this kind of query. I just always placed the null condition last in the OR statement and it always worked fine until I reversed it. Maybe it is a bug but that is so rare i just assumed it was something i was doing wrong or a lack of understanding on my part.

>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 - 09:26:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US