How can I write a custom sql on oracle and load the result to postgres?
Diyotta provides an option to run Custom SQL at dataflow level, using below options
When using custom queries to be run on source instance in a data flow it is required that the order of the columns and name of the columns in the select sql should match the source attributes. For this you can either create a data object of the source system type using “Create Data Object” option and add the attributes to match the columns in the custom SQL to be run OR create copy of existing data object and modify the attributes to match the columns in the custom SQL to be run. After this is done follow below steps.
1. Create a dataflow with Oracle as a source (use the data object created above) and Postgres as a target.
2. Then move to the Source–>Properties–>Enable Override Source Query and select YES from the dropdown.
3. Next, select the Override Source Query expression editor.
4. Then replace the Diyotta generated oracle source SQL with your custom SQL.
5. make sure the order of the columns and name of the columns in the select statement should match the source [oracle] attributes.
5. Then execute the dataflow.