We are on RDM v14.5.1, on PaaS, so underlying db is postgres.

I need to query a table using the RDM Extended Reader step with a filter. The volume of this table will grow, so I much prefer to filter at the reader step, rather than bring the whole table into the plan and then filter, to manage performance.

I have a column, from_date, that is datetime type. I have from_date in my data flow, and I want to query RDM for a day prior.

Expecting that this is an SQL where clause, I tried to put in syntax compatible with Postgres

So I created a column called “row_filter”, with type string.

1. Using postgres to_timestamp function

day_prior:=dateAdd(from_date,-1,'DAY');
day_prior_string:=tostring(day_prior,'yyyy-MM-dd HH:mm:ss');

"from_date=to_timestamp('"+day_prior+"','YYYY-MM-DD HH:MI:SS')"

This one gave an error of:

13.05.2024 13:10:00 :INFO]     Ataccama DQC engine initialized.
13.05.2024 13:10:00 :INFO]     Creating runtime...
13.05.2024 13:10:00 :INFO]     Starting runtime...
13.05.2024 13:10:00 :INFO]     Running runtime...
13.05.2024 13:10:01 :FATAL]    **** missing message: rdm.sql.parse.error ****oSTEP exchange_rategRdm Synchronize]]
13.05.2024 13:10:01 :INFO]     Stopping runtime...
13.05.2024 13:10:02 :INFO]     Finished!

0,1:Unknown function: 'to_timestamp'

2. So I thought, I’ll just pass in the string and see what happens:

    

   day_prior:=dateAdd(from_date,-1,'DAY');
   day_prior_string:=tostring(day_prior,'yyyy-MM-dd HH:mm:ss');
   
   "from_date='"+day_prior+"'"

   This one gave an error of:

   9,1:Incompatible types.

    

3. So definitely doesn’t like in plain string. Does it want DQC syntax?

    

   day_prior:=dateAdd(from_date,-1,'DAY');
   day_prior_string:=tostring(day_prior,'yyyy-MM-dd HH:mm:ss');
   
   "from_date=(toDateTime'"+day_prior+"','yyyy-MM-dd HH:mm:ss')"

   This one gave an error of:

   21,30:Syntax error

    

4.  What about the postgres :: operator?

   day_prior:=dateAdd(from_date,-1,'DAY');
   day_prior_string:=tostring(day_prior,'yyyy-MM-dd HH:mm:ss');
   
   "from_date='"+day_prior+"'::timestamp"

   This one gave an error of:

   0,0:Syntax error
   0,0:Syntax error
   42,9:Syntax error

    

5. How about the postgres CAST() function?  

   day_prior:=dateAdd(from_date,-1,'DAY');
   day_prior_string:=tostring(day_prior,'yyyy-MM-dd HH:mm:ss');
   
   "from_date=CAST('"+day_prior+"' as timestamp)"

   This one gave an error of:

   14,1:Syntax error
   10,4:Column 'CAST' does not exist.

    

What is the syntax supposed to be? I feel like I’m just not using the right language…

Attached an example plan I’m trying to run.