topic Re: Replicate Full load task with pass through filter condition in Qlik Replicate

Replicate Full load task with pass through filter condition

NewbieQlik — Tue, 29 Nov 2022 18:46:00 GMT

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.

Re: Replicate Full load task with pass through filter condition

KellyHobson — Tue, 29 Nov 2022 19:28:58 GMT

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

Re: Replicate Full load task with pass through filter condition

NewbieQlik — Tue, 29 Nov 2022 19:31:50 GMT

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

Re: Replicate Full load task with pass through filter condition

NewbieQlik — Tue, 29 Nov 2022 20:14:38 GMT

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.

Re: Replicate Full load task with pass through filter condition

NewbieQlik — Tue, 29 Nov 2022 22:28:41 GMT

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.

Re: Replicate Full load task with pass through filter condition

KellyHobson — Tue, 29 Nov 2022 22:38:05 GMT

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

Re: Replicate Full load task with pass through filter condition

KellyHobson — Wed, 30 Nov 2022 17:19:57 GMT

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

Re: Replicate Full load task with pass through filter condition

NewbieQlik — Wed, 30 Nov 2022 18:29:57 GMT

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

Re: Replicate Full load task with pass through filter condition

KellyHobson — Wed, 30 Nov 2022 18:52:13 GMT

Hey @NewbieQlik ,

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

Best,

Kelly