Replicate Full load task with pass through filter condition

[NewbieQlik]

I have created a full load replicate task with below passthrough filter 

REC_VLD_TO_TS NOT LIKE '%31-DEC-99 12.00.00.000000000 AM%'
AND TRUNC(REC_CREN_TS)<='31-DEC-18'

For some reason the full load task ran for 2 hours and it completed, but it didn't load any record in the target table. I Opened the replicate task log and got the select statement which has the passthrough filter condition and I ran the query against the oracle DB and I could see records. But some reason replicate is unable to process any records and I suspect the issue is because of the passthrough condition.

Source for full load task is: Oracle

Target for full load task is: Snowflake

I wanted to use REC_VLD_TO_TS <> '31-DEC-99 12.00.00.000000000 AM'
AND TRUNC(REC_CREN_TS)<='31-DEC-18' but the replicate take is failing with this error message ORA-01847: day of month must be between 1 and last day of month [1020417] (oracle_endpoint_unload.c:171). But the query with <> condition works fine on oracle side.

Any help on this topic is greatly appreciated.

[KellyHobson]

Hey @NewbieQlik ,

Are REC_VLD_TO_TS  and REC_CREN_TS date fields?

Is it possible to test the filters one at a time to see which one is not working? 

Best,

Kelly 

[NewbieQlik]

REC_VLD_TO_TS -->TIMESTAMP(6) and REC_CREN_TS-->TIMESTAMP(6). Yes I am testing that just with REC_CREN_TS condition.

[NewbieQlik]

Same behavior. I tried just with  REC_CREN_TS and the task ran for 1 hour and it finished. But no records in the target table.

[NewbieQlik]

Looks like TRUNC(REC_CREN_TS)<='31-DEC-18' condition is causing the problem.  I am now trying with TRUNC(REC_CREN_TS)<=TO_DATE('31-DEC-18','DD-MON-YY') to rule out the date conversion issue.

[KellyHobson]

Hey @NewbieQlik ,

Good job narrowing it down!  Try with and without the TRUNC() function.  I have not seen a full load passthru filter with a function in it before, so wondering if that may cause the issue. 

Will try and test out with it internally tomorrow as well.

Best,

Kelly 

[KellyHobson]

Hey @NewbieQlik ,

Can you please try with this filter where it is still in timestamp format?

REC_CREN_TS<='2018-12-31 00:00:00.000'

When I tried with

test1:
TRUNC(REC_CREN_TS)<='31-DEC-18'

and then

test2:
REC_CREN_TS<='31-DEC-18'

The task completed successfully but no data made it to the target.

test3:
I also tried TRUNC(REC_CREN_TS)<='2018-12-31 00:00:00.000'

but it threw "ORA-01830: date format picture ends before converting entire input string", so not entirely sure how TRUNC is being handled by Replicate.

Best,
Kelly

[NewbieQlik · Accepted solution]

It worked fine when I tried with TRUNC(REC_CREN_TS)<=TO_DATE('31-DEC-18','DD-MON-YY') in passthrough filter condition.

[KellyHobson]

Hey @NewbieQlik ,

Great! Thanks for following up and letting us know the syntax which worked for you.

Best,

Kelly