0

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Juan Ignacio Durante
  • 107
  • 1
  • 12
  • Side note: your date math is flawed. If the current date is a greater day-of-month than the previous-month supports, [you're going to end up in the wrong month](https://gist.github.com/Clockwork-Muse/ee76389b511c8ce25b847ab0fe3db9f0). You need to get the start-of-month first, since all months start with 1. Additionally, using `DATE` on the timestamp column is going to make the system ignore any indices: you should be using `shipment_tms >= TIMESTAMP(, '00:00:00')` instead. – Clockwork-Muse Oct 20 '17 at 16:11
  • Hi, that is a valid scenario. However, this is executed on the same date of the month (the 13th) so it will always have the same values and pattern. Thanks – Juan Ignacio Durante Oct 24 '17 at 20:57
  • `However, this is executed on the same date of the month (the 13th) so it will always have the same values and pattern.` - Sure. _For Now_. Always try to make your code able to work regardless of (some) changes to the current environment. Running on a different date is a simple environment changes. Note that the current code you're using can't be reliably tested on the last day of certain months! Which might be a concern for you. – Clockwork-Muse Oct 24 '17 at 21:02

1 Answers1

1

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$

Angelia Song
  • 26
  • 1