Getting wrong sql syntax Error while fetching data from MySQL.

mbp198 — Wed, 10 Feb 2016 21:11:34 GMT

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.

Re: Getting wrong sql syntax Error while fetching data from MySQL.

sunny_talwar — Wed, 10 Feb 2016 21:23:58 GMT

May be try this:

LET vUpdateDate = Chr(39) & Peek('CPUDT',-1,'BKPF') & Chr(39);

LET vUpdateDate = Chr(39) & Date(Peek('CPUDT',-1,'BKPF'), 'DD-MMM-YYYY') & Chr(39);

Re: Getting wrong sql syntax Error while fetching data from MySQL.

mbp198 — Wed, 10 Feb 2016 21:45:51 GMT

Thank you very much, I did not able to figure out that date was an issue..

topic Re: Getting wrong sql syntax Error while fetching data from MySQL. in QlikView

Getting wrong sql syntax Error while fetching data from MySQL.

Re: Getting wrong sql syntax Error while fetching data from MySQL.

Re: Getting wrong sql syntax Error while fetching data from MySQL.