Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: Using OR in an outer join
In article <7j0qo2$6gj$1_at_nnrp1.deja.com>,
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)
>
> A NULL Ending Date means the record cannot be
> assigned a known value for Endning Date.
>
> 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.
Why are you using an outer join? Don't the above rules translate into a condition clause directly as
WHERE EffDate <= SYSDATE
AND ( EndDate IS NULL OR EndDate > SYSDATE )
? That gives you all the Active records. Then you can use NVL() in your
join to other tables.
AND NVL(EndDate, SYSDATE+1) > othertable.column
Does that solve your problem?
>
> 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
> 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?
>
> Thank-you, Brian Mrkonjic
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>
--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Jun 04 1999 - 07:26:01 CDT