where not exists problem

[Ori]

hi everyone,

i'm in need of some dire help.

the problem arises when we use "where not exists" for incremental load purposes.

we have one qvd with the fields id and assignee pattern.

something of this sort.

id	assignee_pattern
1	1.assignee 1, 2. assignee 2, 3. assignee 1
2	1.assignee 2, 2. assignee 1, 3. assignee 2
3	1.assignee 4, 2. assignee 5, 3. assignee 1
4	1.assignee 5, 2. assignee 4, 3. assignee 2
5	1.assignee 2, 2. assignee 3, 3. assignee 4
6	1.assignee 7, 2. assignee 8, 3. assignee 9

our incremental load takes this original table,

loads records that were updated since yesterday and then uses :

CONCATENATE (assignee_pattern)
LOAD *
FROM [lib://$(vFolder_Connection)/$(vEnv)/assignee_pattern.qvd]
(qvd)
WHERE NOT Exists(id);

we're experiencing erratic behavior, 

sometime it works just fine and at other times it drops all the original data and keeps only the new records.

we're really stomped and would appreciate all the help we could get.

many thanks.

[Or]

It's quite hard to guess what the problem might be, but I'd suggest investigating the "loads records that were updated since yesterday" aspect. That sounds like the most likely point of failure.

[marcus_sommer]

I think there are various causes possible, for example the qvd with the historic data is empty (previous storing wasn't successful, other processes access the qvd, too and/or are changing the content, the load runs into an error (any ERRORMODE set ?), something strange within your storage (restoring old data, whatever), ... or there are other loads in beforehand which load this id-field already (exists is only field-relevant and not field within a table).

The document-log should make these things obvious. To make some checks easier you may include some TRACE statement which count the number of records from the various sources and/or loads and/or storing any variable-values which might be in use in loops or to branch into different versions and similar stuff. Adding a timestamp-information to the log-filename would be useful to compare multiple log-files (otherwise they would be overwritten).

- Marcus  

[oskartoivonen]

Whenever a solution appears to be working fine one day and being erratic the other day, it's usually indicative that the script itself is fine, but the data flowing through is behaving different from your assumptions. You need to make sure you understand the data and how it's being updated. The final solution might be an addition to the script to handle some exceptional case or feedback to the source system to fix an issue.