Search code examples
sqldb2ibm-clouddataconnect

Custom filter in DataConnect

As you may have realized, I am using a lot this IBM Bluemix DataConnect service. When creating an activity, you can refine the data you are going to export, using a custom filter (it enables a WHERE clause to add logic to the query)

I am trying to filter a TIMESTAMP field, trying to get 1 month old data, but the sentence is not working.

DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS

I get the following error message

Custom Filter failed. Retry the shaping operation you just performed. •Filter rows by using the SQL WHERE clause expression 'DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS' java.lang.RuntimeException: [1.36] failure: end of input expected DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS

It may be that DATE() or DAY() are not recognized by the service. Did anyone attempt to do such filter?

Thanks

Solution

  • Data Connect does not support the above SQL. Data Connect only supports Spark SQL.

    This is the general page from spark: https://spark.apache.org/docs/1.6.0/sql-programming-guide.html

    This is the list of functions supported by spark: https://spark.apache.org/docs/1.6.0/api/scala/index.html#org.apache.spark.sql.functions$