Re: Q: How do I avoid overlapping values?

The problem is in the table structure...

If your date ranges have no gaps (like when the price changes on one day, the old price is no longer valid) then you have too many columns, and the data stored is redundant...

one option is to ignore the dateTo column entirely, sort the returned rows by the dateFrom field, and apply the maximum price that is less than the cutoff date your looking at.

hope it helps.
Randy

Bjørn Augestad wrote:

> Greetings,
>
> Does anyone know a good way to solve the following problem, using Oracle 8.1.5?
>
> I have a table called foo, with 3 columns, dateFrom, dateTo and price, looks something like this:
> create table foo (
> dateFrom date,
> dateTo date,
> price number
> );
>
> The table contains multiple rows, and what I need to avoid is that 2 rows overlap.
> dateFrom dateTo price
> Row 1: '01/01/1990' '12/12/1990' 100.00
> Row 2: '02/02/1990' '02/02/1991' 120.00
>
> The rows above tries to illustrate the problem, that both rows contains a (different) price for the same date range(02/02 to 12/12).
>
> I know that I can use triggers to test for overlap, but that solution doesn't scale, and the table may contain 1M rows or more.
>
> TIA
> Bjørn Augestad
Received on Sat Jun 05 1999 - 17:47:55 CDT