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...):
where /BIC/Z99CDAOR GE '20140501'
and /BIC/Z99CDAOR LT '20140601'
GE means in SAP ABAP 'Greater or Equal' : >=
LT means 'Less Than' : <
It shouldn't be difficult to maintain variables for these filters
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
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.