Incremental Load Error

[#1]

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,

[#2]

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,

[sunny_talwar]

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.

[pooja_sn]

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?

[#5]

Please find attached the text file.

Thanks,

[pooja_sn]

Check  your database table to confirm that you have records having ' updatedate ' actually greater than $(vLastRunTime).

[sunny_talwar · Accepted solution]

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;

[#8]

Because that's the primary key needed for insert and update incremental load.

[#9]

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;

[sunny_talwar]

Are you sure that pkdim1customercuration is unique identifier from your table?

[#11]

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.