topic Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column? in Qlik Replicate

How do you replicate an Oracle table to MySQL using filter on a date dataype column?

desmondchew — Thu, 14 Oct 2021 13:08:09 GMT

I have setup a couple of Oracle tables replicating into MySQL.

There's some huge tables where I'd like to take just three years of data.

How can I do like

select * from tableA where to_char(aud_date,'YYYY') > 2018; ?

I tried to put them into expression builder but failed. I could however filter column data by values. I did so under table Settings --> Filter --> Filter Conditions --> Equal to ranges = xyz value.

Please let as the table is huge to transfer the data across.

Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column?

john_wang — Thu, 14 Oct 2021 13:57:56 GMT

Hello @desmondchew ,

Well, you are almost there.

Let's say the query running fine in SQL Developer for example:

select * from emp where to_char(hiredate,'YYYY') > '2019';

Now you want to pass the where condition to Oracle source to filer some rows, you can press <Ctrl> key and at the same time double click the table to open the table setting window, paste the expression to the area of "Fullload Passthrough Filter" like:

you can compose any expression to filter the rows.

Hope this helps.

Regards,

John.

Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column?

desmondchew — Thu, 14 Oct 2021 14:53:16 GMT

Thanks for the tip on fulload passthru filter. I do the exact way however it still doesn't allow me to get through. Looks strange.

[TRANSFORMATION ]T: Transformation on table CHEWCH.T1 exists (manipulation_manager.c:630)
00011800: 2021-10-14T10:48:46 [TRANSFORMATION ]T: Transformation on table CHEWCH.T1 exists (manipulation_manager.c:630)
00011800: 2021-10-14T10:48:46 [TRANSFORMATION ]T: Set transformation for table 'CHEWCH.T1' (manipulator.c:1113)
00011800: 2021-10-14T10:48:46 [TRANSFORMATION ]T: Table fullload passthru filter 'to_char(aud_ld_dt,'YYYY') >= '2021';' (manipulator.c:1629)
00011800: 2021-10-14T10:48:46 [SOURCE_UNLOAD ]I: Table 'CHEWCH.T1' is unloaded using the passthrough filter expression 'to_char(aud_ld_dt,'YYYY') >= '2021';' (endpointshell.c:3616)
00004144: 2021-10-14T10:48:46 [TASK_MANAGER ]I: Task error notification received from subtask 1, thread 0, status 1020414 (replicationtask.c:3430)
00011800: 2021-10-14T10:48:46 [SOURCE_UNLOAD ]E: ORA-00911: invalid character [1022307] (oracle_endpoint_unload.c:171)
00011800: 2021-10-14T10:48:46 [SOURCE_UNLOAD ]E: Failed to init unloading table 'CHEWCH'.'T1' [1022307] (oracle_endpoint_unload.c:381)
00011800: 2021-10-14T10:48:46 [SOURCE_UNLOAD ]E: Endpoint is disconnected [1020414] (endpointshell.c:3799)
00011800: 2021-10-14T10:48:46 [SOURCE_UNLOAD ]E: Error executing source loop [1020414] (streamcomponent.c:1914)
00011800: 2021-10-14T10:48:46 [TASK_MANAGER ]E: Stream component failed at subtask 1, component st_1_odstest [1020414] (subtask.c:1396)

Thanks
Desmond

Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column?

john_wang — Thu, 14 Oct 2021 15:10:57 GMT

Hi Desmond,

Sounds like you added an additional ";" (semi-colon) at the end of your expression. please delete it.

And please clean the area (to remove all unnecessary spaces and other chars etc) and then input the expression again. Or send us the task exported JSON file we'd like to check it for you.

Good luck,

John.

Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column?

desmondchew — Thu, 14 Oct 2021 15:26:04 GMT

It worked like a charm. You made my day! Thank you so much John.

Desmond

Re: How do you replicate an Oracle table to MySQL using filter on a date dataype column?

john_wang — Thu, 14 Oct 2021 15:29:16 GMT

Glad to hear that, Desmond 🙂 thank you.