Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, we connect to SAP BW with SAP SQL Connector
We have the field “/BIC/Z99CDAOR” in a ODS in SAP BW. It contains dates (example 01.05.2014). We have to do an incremental load and I have to filter by YYYYMM.
We want to transform the field “/BIC/Z99CDAOR” to the YYYYMM format in order to do the filter. How can we do it?
F_ORDER_DATA:
SELECT *
FROM /BIC/AZ99OS00700
where /BIC/Z99CDAOR = '201405'; //of course it does not match!!
Thanks in advance.
1. Add the following variables to your script:
let vCurrMonth = date(today(),'YYYYMM')&'01' ; //Current Month YYYYMM01
let vNextMonth = date(addmonths(today(),1),'YYYYMM')&'01' ; /Next Month YYYYMM01
F_ORDER_DATA:
SELECT *
FROM /BIC/AZ99OS00700
where /BIC/Z99CDAOR GE '$(vCurrMonth)'
and /BIC/Z99CDAOR LT '$(vNextMonth)' ;
It will work.
2+3. I am not familiarized with any documentation about allowed syntax in the QV SQL Connector. Generally, it is quite similar the ABAP SQL syntax. You can google: 'ABAP SQL Syntax' in order to learn more details.
Hi Yoca
I am not sure that you can use string/text/date functions in the Where clause in SAP SQL statement.
However, I can suggest to use date ranges. For example of you wanna extract 201405, just extract every date between 20140501 and 20140601:
Try this code, it should work (works for me...):
F_ORDER_DATA:
SELECT *
FROM /BIC/AZ99OS00700
where /BIC/Z99CDAOR GE '20140501'
and /BIC/Z99CDAOR LT '20140601'
BTW:
GE means in SAP ABAP 'Greater or Equal' : >=
LT means 'Less Than' : <
It shouldn't be difficult to maintain variables for these filters
Yaniv
Hi,
Yaniv, thank for your answer!!
3 more questions 🙂
1) We have the code to extract by YYYYMM and we cannot use it to extract by dates YYYYMMDD. Do you know another workaround to do the SQL comparing with a YYYYMM parameter?
2) Do you know what is the sintaxis for BETWEEN. We are using the SAP SQL Connector and when we use the BT sintax (where /BIC/Z99CFEPE BT ‘20131201’ ‘20131212’) we receive the error “BT: invalid relational operator":
3) Do you have a manual with the full SQL sintaxis for SAP SQL Connector? We downloaded the manual from the Qlik site: “QlikView Connector for Use with SAP NetWeaver” but in that manual I am afraid that only explains the SQL sintaxis for SAP DSO Connector.
Thanks in advance.
1. Add the following variables to your script:
let vCurrMonth = date(today(),'YYYYMM')&'01' ; //Current Month YYYYMM01
let vNextMonth = date(addmonths(today(),1),'YYYYMM')&'01' ; /Next Month YYYYMM01
F_ORDER_DATA:
SELECT *
FROM /BIC/AZ99OS00700
where /BIC/Z99CDAOR GE '$(vCurrMonth)'
and /BIC/Z99CDAOR LT '$(vNextMonth)' ;
It will work.
2+3. I am not familiarized with any documentation about allowed syntax in the QV SQL Connector. Generally, it is quite similar the ABAP SQL syntax. You can google: 'ABAP SQL Syntax' in order to learn more details.