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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Question: Using OR in an outer join

Re: Question: Using OR in an outer join

From: Ed Prochak <prochak_at_my-deja.com>
Date: Fri, 04 Jun 1999 12:26:01 GMT
Message-ID: <7j8gkj$bdk$1@nnrp1.deja.com>


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

Original text of this message

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