Thank you for responding @asotolongo & @nshah!
We are using oracle 11 for our old database. The new source and target database are in different machine and both are using EDB with postgres 10.
Here is query that we use as a test:
SELECT d_br.branch_key, d_br.outlet_id, d_br.outlet_code, d_br.outlet, d_br.branch_class_id, d_br.branch_id, d_br.branch_code, d_br.branch, d_br.region_id, d_br.region_code, d_br.region, d_br.smho, bc.code AS branch_class, lc.latitude, lc.longitude, lc.radius_dalam, lc.radius_luar
FROM ( SELECT b.outlet_id AS branch_key, b.outlet_id, b.outlet_code, b.outlet, b.branch_class_id, b.branch_id, b.branch_code, b.branch, b.region_id, b.region_code, b.region, COALESCE(u.user_name, 'WITHOUT OUTLET'::character varying) AS smho
FROM ( SELECT outlet.branch_id AS outlet_id, outlet.code AS outlet_code, outlet.name AS outlet, branch.branch_class_id, branch.branch_id, branch.code AS branch_code, branch.name AS branch, korwil.branch_id AS region_id, korwil.code AS region_code, korwil.name AS region
FROM branch@mfdg outlet
JOIN branch@mfdg branch ON branch.branch_id =
CASE
WHEN outlet.branch_type::text = 'BRANCH'::text THEN outlet.branch_id
ELSE outlet.parent_id
END
JOIN branch@mfdg korwil ON korwil.branch_id =
CASE
WHEN branch.branch_type::text = 'KORWIL'::text THEN branch.branch_id
ELSE branch.parent_id
END) b
LEFT JOIN ( SELECT ub.branch_id, u.user_name
FROM user_branch@mfdg ub
JOIN users@mfdg u ON ub.user_id = u.user_id
JOIN user_role@mfdg ur ON ur.user_id = u.user_id AND ur.role_id = 92) u ON b.outlet_id = u.branch_id) d_br
LEFT JOIN lokasi_cabang@mfdg lc ON d_br.outlet_id = lc.branch_id
LEFT JOIN branch_class@mfdg bc ON d_br.branch_class_id = bc.branch_class_id;
