Date filter with expression builder crashes Full Load (Oracle → BigQuery)

[Domenico_Arcamone]

1. Hi all,

   I have a Qlik Replicate task (Full Load only, Oracle → BigQuery) on 2 tables (Table A and Table B). I want to filter only "yesterday's" data on a date column.

   Attempt 1: filter as an expression

   date($DAT_RIFERIMENTO) = date('now', 'localtime', '-1 day')

   This doesn't push down to Oracle (stays on the transformation side), so it scans the whole table (billions of rows) and after a few hours crashes with:

   ORA-08103: object no longer exists

   Attempt 2: rewrote it as a range

   $DAT_RIFERIMENTO >= date('now', 'localtime', '-1 day') AND $DAT_RIFERIMENTO < date('now', 'localtime')

   This time it does push down, but the generated query toward Oracle is wrong:

   sql

   WHERE ("DAT_RIFERIMENTO" = TO_DATE('0000-00-00','YYYY-MM-DD')) OR ... (repeated 3 times)

   → ORA-01843: not a valid month

   Questions:

   1. Has anyone hit this translation bug with date('now',...) in the source filter pushdown toward Oracle?
   2. What's the correct way to write a relative date filter that translates properly into TO_DATE()?
      
      

   Thanks!

[-mjl- · Accepted solution]

Hi, Have you considered using the hidden Full Load Pass Through filter? Using that you could try adding something like my_date_col >= TRUNC(SYSDATE) - 1 AND my_date_col <  TRUNC(SYSDATE)

Added benefit is you are not shifting the entire table to the Replicate server for filtering. An index on your date column on Oracle be recommended. Cheers!

[john_wang]

Hello @Domenico_Arcamone ,

As @-mjl- mentioned, the passthru filter can be used.

The detailed explanation can be found at: https://community.qlik.com/t5/Qlik-Replicate/Pass-thru-filter/m-p/2411372#M9081

Hope this helps.

John.