RE: anyone know how to do a join where the data can be different
- From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
- To: "dba.russ@xxxxxxxxx" <dba.russ@xxxxxxxxx>, "david@xxxxxxxxxxxxxxxxxx" <david@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 11 Jun 2008 12:32:08 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Russ Brewer
Sent: Wednesday, June 11, 2008 10:02 AM
To: david@xxxxxxxxxxxxxxxxxx
Cc: Mark.Bobak@xxxxxxxxxxxx; ricks12345@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx<mailto:david@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>>
To: "ricks12345@xxxxxxxxx<mailto:ricks12345@xxxxxxxxx>"
<ricks12345@xxxxxxxxx<mailto:ricks12345@xxxxxxxxx>>;
"oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>"
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxxxxx<mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com<http://www.proquest.com/>
www.csa.com<http://www.csa.com/>
ProQuest...Start here.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Rick Ricky
Sent: Wednesday, May 14, 2008 2:32 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
Other related posts:
