Sorry Svein, I explained the problem badly.

UNQ + DATE IS UNIQUE CONSTRAINT
UNQ DATA DATE
1 A 1/1/2006
2 B 1/1/2005
3 C 1/1/2004
1 A 1/1/2003
2 B 1/1/2002

I should detect an insert of (2, B, 3/3/2005) as nothing is changing,
the catalog file is just telling me the same thing again with a later
date.
I should allow an insert of (2, B, 3/3/2006) as the values as of
1/1/2006 have reverted back to their 1/1/2005 state.
I'm worried that the BETWEEN condition will pick up rows that aren't
immediately before or after (ordered by date) row I'm inserting.

While waiting to fall asleep last night I was wondering if I should even
exclude these. It's possible there is a data point between the two
identical items, meaning I can't collapse them. The answer would be for
the vendor to fix the issue on their end since they have the data, but
that has no chance of happening.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Friday, October 06, 2006 3:04 AM
To: Firebird-support
Subject: Re: [firebird-support] Help with query

What about 'nested exists', Rick?

where exists(
Select 1
From
catalog c
Where
c.col1 = :new_col1 And
c.col2 = :new_col2 And
...
c.colN = :new_coln and
not exists(
select * from
catalog c2
where
c2.catalog_id = c.catalog_id and /*whatever is required to find the
right item */
c2.pk <> c.pk and /*to avoid no matches*/
c2.new_date between c.new_date and :new_date)) /*This is the punch
line, it checks to see that there aren't any records between c.new_date
and :new_date */

I'm pretty certain that you only want to use the index for the id of the
catalog item in the innermost select. Unless you have very few items and
all of them have lots of records, this nested exists shouldn't be too
much slower than with only a simple exists clause.

HTH,
Set

Rick Debay wrote:

> I've found that vendors are sending us updates to items, where nothing

> changes from the data we already have except for the effective date of

> the change. Some of them are updating large portions of their product

> catalog this way, making me think they took the easy way out of
> sending us the date of the extract on every row, instead of the actual

> date the particular item in the catalog changed.
> I'd like to silently drop these. The problem is coming up with the
> following query (which will live in an exists conditional):
>
> /* see if there are any exact matches for any item who's date is
> immediately before or after ours */ Select 1 From
> catalog c
> Where
> c.col1 = :new_col1 And
> c.col2 = :new_col2 And
> ...
> c.colN = :new_coln and
> (:new_date >= date_previous_to_new_date and :new_date <=
> date_after_new_date)
>
> I could do the last part as something like (I know the max/min ><
> stuff isn't correct):
>
> :new_date >= select min(date_col) where pk_col=:new_pk_col and
> date_col < :new_date
>
> But that seems quite inelegant.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links