Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm trying to do a Incremental Insert and Update Load on Update Date. I'm getting a syntax error. Can someone provide assistance.
Thanks in Advance,
Can someone please provide assistance regarding the above. Could it be because I'm resaving (replacing) the incremented QVDs file in the save folder as my original QVDs were stored?
Thanks,
Would you be able to post script as a text file instead of a screenshot. It makes it difficult to use the code you have.
Updating the same incremented QVDs file will definitely not cause this issue. Please share your script if you can.
Why have you included " WHERE not Exists(pkdim1customercuration);" in script?
Please find attached the text file.
Thanks,
Check your database table to confirm that you have records having ' updatedate ' actually greater than $(vLastRunTime).
Try this in 2 reloads:
1) reload with this script
SET vQVDLocation=' C:\QV Project\Laura\Data';
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
ODBC CONNECT TO ExcessionProd;
CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration;
STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
DROP Table CustomerCuration;
2) once you have the qvd created, comment out the above script and replace it with this one
//SET vQVDLocation=' C:\QV Project\Laura\Data';
//LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
//ODBC CONNECT TO ExcessionProd;
//
//CustomerCuration:
//SQL SELECT *
//FROM "rbc_dw"."open".dim1customercuration;
//
//STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
//
//DROP Table CustomerCuration;
SET vQVDLocation=' C:\QV Project\Laura\Data';
ODBC CONNECT TO ExcessionProd;
CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration
Where updatedate > '$(vLastRunTime)';
Concatenate(CustomerCuration)
LOAD *
FROM customercuration.qvd (qvd)
Where not exists (pkdim1customercuration);
STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
DROP Table CustomerCuration;
Because that's the primary key needed for insert and update incremental load.
Still no fruition Sunny.
The first script gave me the 200K and the second script gave 300 rows.
SET vQVDLocation='..\Data';
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
ODBC CONNECT TO ExcessionProd;
//CustomerCuration:
//SQL SELECT *
//FROM "rbc_dw"."open".dim1customercuration;
//
//STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
//
//DROP Table CustomerCuration;
----------------------------------------------------
CustomerCuration:
SQL SELECT *
FROM "rbc_dw"."open".dim1customercuration
Where updatedate > '$(vLastRunTime)';
Concatenate(CustomerCuration)
LOAD *
FROM customercuration.qvd (qvd)
where not exists (pkdim1customercuration);
STORE CustomerCuration INTO $(vQVDLocation)\CustomerCuration.qvd (qvd);
LET vLastRunTime = Date(Today(), 'DD-MMM-YYYY');
DROP Table CustomerCuration;
Are you sure that pkdim1customercuration is unique identifier from your table?
Yes pkdim1customercuration is the unqiue identifier since it has no null value in the table indicating it has a value for every row in the table.