Rick Debay wrote:

> 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.

OK, lets take your example with nested exists:

2 B 3/3/2005
Does there exist an item 2B with no other item between this and the 2B I
am inserting? Yes, 2B 1/1/2005 exists and there isn't any later insert
until 3/3/2005. Hence, no insert.

2 B 3/3/2006
Does there exist an item 2B with no other item between this and the 2B I
am inserting? No, 1A 1/1/2006 is between 2B 1/1/2005 and the item to
insert. Hence, this item must be inserted.

Think about it, if done properly there is no way for things to leak
through a nested select. Not too long ago, I used lots of nested selects
combined with left joins to flatten a table of children, so that each
parent had one line with date and gender etc. of their children
following in the next fields. I had to use the artificial primary key to
determine which should be the first child in case of twins, triplets
etc., but I think I got it right even though some people had more than
10 children.

> 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.

Sure, if asynchronous updates are happening, e.g. if you in your example
above got a 3C 2/2/2005 after you simply dropped 2B 3/3/2005, then you'd
get wrong data. If that's the scenario, then you cannot drop anything
(although you could add the data to a hidden table, and then only insert
two records when you got your 3C 2/2/2005. Though that would only be
useful in cases where it mattered when the last date of change actually
happened).

Set

> -----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.