Re: REPOST: Outer join and the OR operator

Brian,

In this particular case I would suggest using a 'special' date that is far into the future. Generally I avoid using 'special' values in place of NULL because one still has to check for them anyway, and IMHO once you've come to grips with NULL you might as well just use that. For example, I wouldn't use a special date in the case of 'I don't know the date - even though there is one', or 'this date is not applicable in this case' I feel that NULL is more appropriate in these cases.
The reason I suggest a special value here is that your dates actually represent a period of time, NULL means 'I don't know' to SQL while you really want it to mean 'has no end'. You'll find that your SQL will become a lot simpler using the placeholder value.

Regards, David.

mrkonjic_me_at_my-deja.com wrote:

> Hi,
>
> I am working on a temporal database which uses an
> 'Effective Date' and 'Ending Date' attribute for each
> major entity. An Active (as opposed to an Historic
> or Future) record is defined as follows:
>
> 1. Effective date <= SYSDATE
> 2. (Ending Date IS NULL OR Ending Date > SYSDATE)
>
> When I try to outer join two tables incorporating
> the above constaints, Oracle won't allow me to use
> an OR in the outer join syntax.
>
> I've read the manuals and understand that this is
> as to be expected. My question is, does anyone
> have a work around?
>
> Also, I suspect that it was not a good idea to
> define an Active record as one with either a NULL
> Ending Date OR an Ending Date > SYSDATE, ie two
> definitions for the same thing. What is the common
> practice for specifying an Active record which has
> a NULL Ending Date?
>
> Is it better to replace the use of NULL with a constant
> date value that is far into the future, such as the
> Year 3000 or the maximum date supported by Oracle?
>
> Thank-you, Brian Mrkonjic
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jun 03 1999 - 18:48:59 CDT