FAQ
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
from tab1, tab2
where tab1.id = 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?

Search Discussions

  • Bobak, Mark at May 14, 2008 at 6:41 pm
    How about:
    Select
    From tab1, tab2
    Where tab1.id = 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
    www.proquest.com<http://www.proquest.com>
    www.csa.com<http://www.csa.com>

    ProQuest...Start here.

    From: 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
    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
    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?

    --
    http://www.freelists.org/webpage/oracle-l
  • Jackie Brock at May 14, 2008 at 6:51 pm
    select
    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


    (assuming my math is correct on seconds in a day!)


    -Jackie


    Jackie D. Brock
    Database Specialist - Systems Evaluation
    CableLabs(r)
    858 Coal Creek Circle
    Louisville, CO 80027
    Email: j.brock_at_cablelabs.com
    303-661-3347


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Rick Ricky

    Sent: Wednesday, May 14, 2008 12:32 PM
    To: 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
    from tab1, tab2
    where tab1.id = 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?
  • David Aldridge at May 14, 2008 at 7:11 pm
    It might be more efficient to do something like ...

    Select
    From tab1, tab2
    Where tab1.id = tab2.id
    And tab1.datefield between tab2.datefield) - 5/86400 and tab2.datefield) + 5/86400

    Original Message ----
    From: "Bobak, Mark"
    To: "ricks12345_at_gmail.com"; "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
    From tab1, tab2
    Where tab1.id = 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
    www.proquest.com
    www.csa.com

    ProQuest...Start here.


    From: 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
    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
    from tab1, tab2
    where tab1.id = 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?
    --
    http://www.freelists.org/webpage/oracle-l
  • Russ Brewer at Jun 11, 2008 at 3:01 pm
    What about something like:

    Select

    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

    From tab1, tab2

    Where tab1.id = tab2.id

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


    ----- Original Message ----
    From: "Bobak, Mark"
    To: "ricks12345_at_gmail.com"; "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

    From tab1, tab2

    Where tab1.id = 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
    www.proquest.com
    www.csa.com

    *ProQuest...*Start here.



    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Rick Ricky
    *Sent:* Wednesday, May 14, 2008 2:32 PM
    *To:* 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

    from tab1, tab2

    where tab1.id = 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?

    --
    http://www.freelists.org/webpage/oracle-l
  • Elliott, Patrick at Jun 11, 2008 at 5:32 pm
    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 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

    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

    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" >
    To: "ricks12345_at_gmail.com" >; "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

    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
    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] On Behalf Of Rick Ricky
    Sent: Wednesday, May 14, 2008 2:32 PM
    To: 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

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 14, '08 at 6:31p
activeJun 11, '08 at 5:32p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase