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> wrote:
>
> It might be more efficient to do something like ...
>
>
> Select <col>
>
> 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" <Mark.Bobak_at_proquest.com>
> To: "ricks12345_at_gmail.com" <ricks12345_at_gmail.com>; "oracle-l_at_freelists.org"
> <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 = 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 <mark.bobak_at_il.proquest.com>
> www.proquest.com
> 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 <col>
>
> 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