Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
NewbieQlik
Contributor III
Contributor III

Replicate Full load task with pass through filter condition

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.

1 Solution

Accepted Solutions
NewbieQlik
Contributor III
Contributor III
Author

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

View solution in original post

8 Replies
KellyHobson
Former Employee
Former Employee

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
Contributor III
Contributor III
Author

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

NewbieQlik
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Former Employee
Former Employee

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
Former Employee
Former Employee

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
Contributor III
Contributor III
Author

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

KellyHobson
Former Employee
Former Employee

Hey @NewbieQlik ,

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

Best,

Kelly