RE: anyone know how to do a join where the data can be different
Date: Wed, 11 Jun 2008 12:32:08 -0500
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0F1184CB05@MSPM1BMSGM103.ent.core.medtronic.com>
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
Pat
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Russ Brewer Sent: Wednesday, June 11, 2008 10:02 AM
To: david_at_david-aldridge.com
Cc: Mark.Bobak_at_proquest.com; ricks12345_at_gmail.com; oracle-l_at_freelists.org 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 <david_at_david-aldridge.com<mailto:david_at_david-aldridge.com>> wrote: It might be more efficient to do something like ...
Select <col>
From tab1, tab2
Where tab1.id<http://tab1.id/> = tab2.id<http://tab2.id/>
And tab1.datefield between tab2.datefield) - 5/86400 and tab2.datefield) + 5/86400
- Original Message ---- From: "Bobak, Mark" <Mark.Bobak_at_proquest.com<mailto:Mark.Bobak_at_proquest.com>> To: "ricks12345_at_gmail.com<mailto:ricks12345_at_gmail.com>" <ricks12345_at_gmail.com<mailto:ricks12345_at_gmail.com>>; "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> 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 tab1.id<http://tab1.id/> = tab2.id<http://tab2.id/>
And abs(tab1.datefield-tab2.datefield) <= 5/86400;
-Mark
--
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
mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com>
www.proquest.com<http://www.proquest.com/>
www.csa.com<http://www.csa.com/>
ProQuest...Start here.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Rick Ricky
Sent: Wednesday, May 14, 2008 2:32 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
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 tab1.id<http://tab1.id/> = tab2.id<http://tab2.id/>
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: http://emaildisclaimer.medtronic.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 11 2008 - 12:32:08 CDT