Re: Outer join based on range?

From: Heggelund <d92hegge_at_ix_prod.hfk.mil.no>
Date: 1997/04/02
Message-ID: <1997Apr2.112028.29173@ix_prod.hfk.mil.no>#1/1

Hi

yes you can do an outer join based on a range.

Here is a small example:

create table t1 (date1 date , date2 date); -- This contains date intervalls create table t2 (date3 date); -- This contains a spesific date

insert into t1 values ('01-jan-97','30-jan-97'); insert into t1 values ('01-mar-97','30-mar-97');

insert into t2 values ('10-jan-97');
insert into t2 values ('10-feb-97');
insert into t2 values ('10-mar-97');

commit;

select t1.*, t2.*
from t1,t2
where t2.date3 between t1.date1(+) and t1.date2(+) /

This will return:

DATE1 DATE2 DATE3

---------  ---------  ---------
01-JAN-97  30-JAN-97  10-JAN-97
                      10-FEB-97
01-MAR-97  30-MAR-97  10-MAR-97

Rgds
Steinar Heggelund

Damien Jones (damien_at_seanet.com) wrote:
: Can you do an outer join based on a range (><) rather than an equality?
 

: I need to display a list of ordered products and prices (if available) for
: a date range.
: Products cost different amounts during different time periods.
 

: My price reference table is keyed on product and date range.
: example: product-1; from-3/1/97; to-3/30/97; price-$100
: product-1; from-4/1/97; to-4/31/97; price-$125
: product-2; from-3/1/97; to-3/31/97; price-$450
: ... (thousands of records here)
 

: My order table lists products and dates ordered.
: example: product 1; date-3/15/97
: product 2; date-5/15/97
: ... (note: there will be substantially more than 2 records here)
 

: My query needs to return all products in the order, and prices for the
: products with matching records in the price reference table:
: example: product 1; date-3/15/97; price-$100
: product 2; date-5/15/97; price-(null)
: ...
 

: The only other ideas I can think of involve finding all the products WITH
: prices, using this as a NOT IN query to find products WITHOUT prices, then
: UNIONing these results with another instance of the first query (all
: products WITH prices)
: OR
: Looping through a list of all products and doing a separate query for each
: product.
 

: Since this is a query that will populate a dynamic Web Server page,
: performance is an important issue. The tables accessed are already on slow,
: foreign databases (via DBLINKS), and I believe the last two approaches may
: be speed-prohibitive.
 

: If you have any ideas, they would be greatly appreciated.
 

: Thanks,
: Damien Jones (damien.jones_at_attws.com)

--

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.

Received on Wed Apr 02 1997 - 00:00:00 CST