Re: Question: Using OR in an outer join

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)
>
> <snip>

> I've read the manuals and understand that this is
> as to be expected. My question is, does anyone
> have a work around?
>

My sugestion is to use the NVL function thus:(nvl(Ending Date, sysdate + 1) > SYSDATE)
> 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
> no defined 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?
>

My choice would be to add a 'current' flag to the table maintained by a before insert and before update trigger. This way the ending date cannot be misinterpreted.
<snip> Received on Thu Jun 03 1999 - 20:46:45 CDT