Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to fetch data from MySQL database and trying to perform incremental load the script I am using is
////INITIAL LOAD
ODBC CONNECT TO Mysql_datasource_test;
BKPF_TEMP:
LOAD
//MANDT,
BUKRS&GJAHR&BELNR AS KEY,
BUKRS, //Company Code
BELNR, //DocumentNo
GJAHR, //YEAR
BLART, //Document Type
BLDAT, //Document Date
BUDAT, //Posting Date
MONAT, //Period
CPUDT, //Entered on (To use this for the purpose of incremental load)
XBLNR, //Reference
WAERS, //Currency
KURSF //Exchange rate
;
SQL SELECT *
FROM testtable.bkpf;
where BLART IN ($(vDocTypes));
// Once data Initial data is loaded store the data in a QVD File
Store BKPF_TEMP into $(vSaveQVD)BKPF_TEMP.QVD (QVD);
//// Load BKPF_TEMP QVD to get latest date updated..
BKPF_TEMP:
LOAD
//MANDT,
KEY,
BUKRS, //Company Code
BELNR, //DocumentNo
GJAHR, //YEAR
BLART, //Document Type
BLDAT, //Document Date
BUDAT, //Posting Date
MONAT, //Period
CPUDT, //Entered on (To use this for the purpose of incremental load)
XBLNR, //Reference
WAERS, //Currency
KURSF //Exchange rate
FROM $(vSaveQVD)BKPF_TEMP.QVD (QVD);
// This step is to find the last updated date
BKPF:
LOAD *
Resident BKPF_TEMP
ORDER BY CPUDT;
// Looking at the last updated date
LET vUpdateDate = Peek('CPUDT',-1,'BKPF');
//// You Drop BKPF_TEMP because ordered data is already in BKPF table
DROP TABLE BKPF_TEMP;
ODBC CONNECT TO Mysql_datasource_test;
// Incremental load
BKPF_Incremental:
LOAD
//MANDT,
BUKRS&GJAHR&BELNR AS KEY,
BUKRS, //Company Code
BELNR, //DocumentNo
GJAHR, //YEAR
BLART, //Document Type
BLDAT, //Document Date
BUDAT, //Posting Date
MONAT, //Period
CPUDT, //Entered on (To use this for the purpose of incremental load)
XBLNR, //Reference
WAERS, //Currency
KURSF //Exchange rate
;
SQL SELECT *
FROM testtable.bkpf
where BLART IN ($(vDocTypes)) AND CPUDT >= $(vUpdateDate) ;
//Concatinate incremental load with previously loaded records by adding new records or replacing the updated records
Concatenate
LOAD
//MANDT,
KEY,
BUKRS, //Company Code
BELNR, //DocumentNo
GJAHR, //YEAR
BLART, //Document Type
BLDAT, //Document Date
BUDAT, //Posting Date
MONAT, //Period
CPUDT, //Entered on (To use this for the purpose of incremental load)
XBLNR, //Reference
WAERS, //Currency
KURSF //Exchange rate
FROM $(vSaveQVD)BKPF_TEMP.QVD (QVD)
Where not exists(KEY);
STORE BKPF_Incremental into $(vSaveQVD)BKPF_TEMP.QVD (QVD);
DROP TABLE BKPF_Incremental;
but I am getting Error
I cant figure out what is wrong with this sql.
May be try this:
LET vUpdateDate = Chr(39) & Peek('CPUDT',-1,'BKPF') & Chr(39);
or
LET vUpdateDate = Chr(39) & Date(Peek('CPUDT',-1,'BKPF'), 'DD-MMM-YYYY') & Chr(39);
May be try this:
LET vUpdateDate = Chr(39) & Peek('CPUDT',-1,'BKPF') & Chr(39);
or
LET vUpdateDate = Chr(39) & Date(Peek('CPUDT',-1,'BKPF'), 'DD-MMM-YYYY') & Chr(39);
Thank you very much, I did not able to figure out that date was an issue..