topic Re: where not exists problem in App Development

where not exists problem

Ori — Thu, 17 Nov 2022 11:40:04 GMT

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.

Re: where not exists problem

Or — Thu, 17 Nov 2022 12:03:03 GMT

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.

Re: where not exists problem

marcus_sommer — Thu, 17 Nov 2022 13:27:32 GMT

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

Re: where not exists problem

oskartoivonen — Thu, 17 Nov 2022 20:55:47 GMT

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.