I am having an issue with incremental reload losing data. Below is a section of my script that is causing issues. I have narrowed this issue down to being caused to the WHERE NOT EXISTS part. i.e. I commented that out and the data I want shows up. I checked the first section of the SQL select and the data I want is not there, and the primarykeyJournaldtl is not there. I have no Idea what could be causing this as I have double checked to make sure it is not being loaded in the first section, and I do know that it is contained within the QVD.
This is beginning to give me a major headache so any help would be appreciated.
Let ThisExecTime= num(today());
Let ThisExecTimeyear= year(today());
Let ThisExecTimemonth= num(month(today()));
Let ThisExecTimeday= day(today());
Let LastExecTime = ThisExecTime;
Let LastExecTimeyear=ThisExecTimeyear;
Let LastExecTimemonth=ThisExecTimemonth;
Let LastExecTimeday=ThisExecTimeday;
Journaldtl1:
SQL SELECT
company + bookid + cast(fiscalyear as varchar)+ cast(journalcode as varchar) + cast(journalnum as varchar)
+ cast(journalline as varchar) as primarykeyJournaldtl,
*
FROM gljrndtl where
((CAST(( STR( ( YEAR(posteddate) ) ) + '/' + STR( MONTH(posteddate) ) + '/' + STR( DAY(posteddate) ) ) AS DATETIME) ) >=
CAST(( STR( ( $(LastExecTimeyear) ) ) + '/' + STR( ($(LastExecTimemonth)) ) + '/' + STR(($(LastExecTimeday)) ) ) AS DATETIME)
AND
(CAST(( STR( ( YEAR(posteddate) ) ) + '/' + STR( MONTH(posteddate) ) + '/' + STR( DAY(posteddate) ) ) AS DATETIME) ) <=
CAST(( STR( ( $(ThisExecTimeyear) ) ) + '/' + STR( ($(ThisExecTimemonth)) ) + '/' + STR(($(ThisExecTimeday)) ) ) AS DATETIME) )
;
Concatenate
LOAD
*
FROM [....qvd(qvd)] (qvd)
WHERE NOT EXISTS(primarykeyJournaldtl)
;
Inner Join SQL SELECT company,bookid,fiscalyear,journalcode,journalnum,journalline FROM gljrndtl;
