Hello,
I'm trying to implement incremental loading of data. To start, I took a simple table in one of our SQL-databases.I'm getting errors when reloading the data
The Code :
SET
ThousandSep ='.';
SET DecimalSep =',';
SET MoneyThousandSep ='.';
SET MoneyDecimalSep =',';
SET MoneyFormat ='€ #.##0,00;€ -#.##0,00';
SET TimeFormat ='h:mm:ss';
SET DateFormat ='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';
SET vDB='Dehaan.dbo.Personel';
SET vQvdFile='VTW.qvd';
SET vTableName='VTW';
SET vPK='PersonelId';
LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
IF $(vQvdExists) THEN
maxdateTab:
LOAD max(VTW.ModifyDate) as maxdate
FROM $(vQvdFile) (qvd);
LET vIncrementalExpression = 'WHERE ModifyDate >=' & date(peek('maxdate'),'yyyy-mm-dd');
DROP table maxdateTab;
ELSE
LET vIncrementalExpression = '';
END IF
//==========================================================================================
// Data Load
//==========================================================================================
$(vTableName):
ODBC CONNECT TO BRP (UserId is sa, Password is 9999);
LOAD
Email AS VTW.Email,
FirstName AS VTW.Voornaam,
ModifyDate AS VTW.ModifyDate,
Name,
PersonelId,
PersonelPicture,
Picture,
SearchName,
if(SearchName='EBA','AAU',
if((FirstName='Nathalie' OR FirstName='Nele' OR FirstName='Dan'),'SALMA',
if((SearchName='PVP' OR SearchName='VDP'),'VDP',
if(SearchName='PHU','PHU',
if(SearchName='AHE','AHE',
if(SearchName='AAU','AAU',
if(SearchName='CVE','CVE',
if(SearchName='KVE','KVE',
if(SearchName='LSA','VDP',
if(SearchName='RVDW','PDE',
if(SearchName='SALMA','SALMA',
if(SearchName='Salma','SALMA',
if(SearchName='salma','SALMA',
if(SearchName='Marke','MARKE',
if(SearchName='RRI','RRI',
if(SearchName='PDE','PDE',
if(SearchName='WSCHO','WSC'))))))))))))))))) AS VTW;
SQL SELECT * FROM $(vDB) $(vIncrementalExpression);
//==========================================================================================
// Incremental Load
//==========================================================================================
ODBC CONNECT TO BRP (UserId is sa, Password is 9999);
IF $(vQvdExists) THEN
Concatenate ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists($(vPK));
END IF
INNER JOIN ($(vTableName))
LOAD $(vPK) FROM Dehaan.dbo.Personel;
STORE $(vTableName) INTO $(vQvdFile);
Yes, password sa is 'changed'.
