REPOST: Outer join and the OR operator

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 - 14:50:15 CDT