Can i have oracle and postgres in subselect query inside select ?

[pcouas]

Hello

I'm planning to use ora2pG to migrate a table A.

REPLACE_TABLES tableoracle:table_postgres_1

Columns to keep

COLUMNS table_postgres_1:ref_pays,libelle,code_pays,date_maj,user_maj

REPLACE_QUERY tableoracle[
SELECT TO_NUMBER(a.reference_pays),
a.libelle_pays,
a.code_pays,
a.date_maj,
a.user_maj
FROM tableoracle a
WHERE a.code_pays IS NOT NULL

]

But can I also have a link with another Postgres table, "table2postgres2"?
REPLACE_QUERY tableoracle[
SELECT TO_NUMBER(a.reference_pays),
a.libelle_pays,
a.code_pays,
a.date_maj,

a.user_maj,
(SELECT column
FROM table2postgres2 t2
WHERE t2.id = a.id) AS alias)
FROM tableoracle a
WHERE a.country_code IS NOT NULL
]

Regards
Phil

[darold]

@pcouas, normally you can not do that, the data queried are on Oracle side but normally you should be able to do that from the Oracle data only.

Saying that, I have never tested but if you use oracle_fdw to transfer the data it might be possible to join a local PG table.
Just install oracle_fdw and set FDW_SERVER in ora2pg.conf and give it a try.