RE: anyone know how to do a join where the data can be different

From: Elliott, Patrick <>
Date: Wed, 11 Jun 2008 12:32:08 -0500
Message-ID: <>

While the queries from both Mark and David will return the same results, David's query provides the one that has the potential to perform better. If you put an index on datefield, only his version will use it.

Sorry, Russ, but yours is not correct. For example if tab1.datefield has time value of 21:59 and tab2.datefield has a time value of 22:01, then these two times are within 5 seconds, but...

abs(59-01) = 58

58 is not <= 5


From: [] On Behalf Of Russ Brewer Sent: Wednesday, June 11, 2008 10:02 AM
Cc:;; Subject: Re: anyone know how to do a join where the data can be different

What about something like:

Select <col>

From tab1, tab2

where ABS(TO_NUMBER(TO_CHAR(tab1.datefield,'SS')) - TO_NUMBER(TO_CHAR(tab2.datefield,'SS'))) <= 5;

On 5/14/08, David Aldridge <<>> wrote: It might be more efficient to do something like ...

Select <col>

From tab1, tab2

Where<> =<>

And tab1.datefield between tab2.datefield) - 5/86400 and tab2.datefield) + 5/86400

  • Original Message ---- From: "Bobak, Mark" <<>> To: "<>" <<>>; "<>" <<>> Sent: Wednesday, May 14, 2008 2:41:34 PM Subject: RE: anyone know how to do a join where the data can be different

How about:

Select <col>

From tab1, tab2

Where<> =<>

And abs(tab1.datefield-tab2.datefield) <= 5/86400;



Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059<><><>

ProQuest...Start here.

From:<> [<>] On Behalf Of Rick Ricky Sent: Wednesday, May 14, 2008 2:32 PM
To:<> Subject: anyone know how to do a join where the data can be different

I have two different data sets. They come from two different feeds.

I have an ID field that I can join on that is the same in both. However, I have a date field in each. Here is the catch, the dates can be up to 5 second apart.

So I have

select <col>

from tab1, tab2

where<> =<>

and tab1.datefield is with in 5 seconds of tab2.datefield

anyone know a way to do this without a lot of complex pl/sql?

[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.  

To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser:

-- Received on Wed Jun 11 2008 - 12:32:08 CDT

Original text of this message