Hi,
I'm building up kind of an Incremental Load solution for one of my tables which connects to a SQL server.
I first read my old data from a qvd int ReitekContactsTemp and then I load the new one from the DB table into ReitekContactsTemp_1 . My goal is to append data of ReitekContactsTemp_1 to ReitekContactsTemp excluding duplicates of ReitekContacts_CONTACTID running the script more than once a day.
I tried to implement 2 different scenarios which gave me an error and I'd like you to help me in order to understand why:
Scenario 1) SQL Error due to the NOT EXIST condition. Obiously it is wrong but I really did not know how to deal with WHERE NOT EXISTS(ReitekContacts_CONTACTID); which should apply to the concatenation and not to the SQL query.
ReitekContactsTemp:
LOAD * from $(qvdCCarePath)ReitekContacts.qvd (qvd);
CONCATENATE (ReitekContactsTemp)
ReitekContactsTemp_2:
select
....
FROM
contact.contacts c
where ACCEPTED in ('Y','N','X')
and to_char(GDAY,'YYYY-MM-DD')='$(Yesterday)'
WHERE NOT EXISTS(ReitekContacts_CONTACTID);
Scenario 2)Table not found. Automatically data from SQL query is appended to the ReitekContactsTemp table as they have the same fields names. In this case i cannot apply the condition WHERE NOT EXISTS(ReitekContacts_CONTACTID) to the concatenation.
Let Yesterday=date(Today()-1);
ReitekContactsTemp:
LOAD * from $(qvdCCarePath)ReitekContacts.qvd (qvd);
ReitekContactsTemp_1:
select
...
FROM
contact.contacts c
where ACCEPTED in ('Y','N','X')
and to_char(GDAY,'YYYY-MM-DD')='$(Yesterday)';
CONCATENATE (ReitekContactsTemp)
ReitekContactsTemp_2:
Load * Resident ReitekContactsTemp_1
WHERE NOT EXISTS(ReitekContacts_CONTACTID);
Temporary solution:
I Changed the script so it runs once a day and i am sure that it won't insert twice the same line.
Let Yesterday=date(Today()-1);
ReitekContactsTemp:
LOAD * from $(qvdCCarePath)ReitekContacts.qvd (qvd);
ReitekContactsTemp_1:
select
...
FROM
contact.contacts c
where ACCEPTED in ('Y','N','X')
and to_char(GDAY,'YYYY-MM-DD')='$(Yesterday)';
