Can't use the CASE WHEN statement in table description

[sggrigoriev]

Hi! I'm trying to avoid NULL coordinates in Postgres geometry type during the Oracle -> Postgres data import.

Here is the server definition: DROP SERVER IF EXISTS ms_test CASCADE; CREATE SERVER ms_test FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//xx.yy.zz.qq:pppp/orcl', isolation_level 'read_only'); GRANT USAGE ON FOREIGN SERVER ms_test TO my_user; CREATE USER MAPPING FOR my_user SERVER ms_test OPTIONS (user 'XXX', password 'YYY');

DROP FOREIGN TABLE IF EXISTS ms_virt.request;

Here is the table definition: CREATE FOREIGN TABLE ms_virt.request ( request_id numeric OPTIONS (key 'true') NOT NULL, geoloc geometry ) SERVER ms_test OPTIONS ( schema 'CITYLIGHT', table '(SELECT ' 't.request_id,' 'CASE WHEN (t.geoloc.sdo_point.x IS NULL) THEN NULL ELSE t.geoloc END AS g ' 'FROM CITYLIGHT.REQUEST t)' );

The request select * from ms_virt.request;

got this: [HV00M] ERROR: error describing remote table: OCIStmtExecute failed to describe table Detail: ORA-00903: invalid table name

I have oracle_fdw 2.6.0, PostgreSQL 16.2, Oracle client 21.14.0.0.0, Oracle server 19.0.0.0.0 oracle_fdw 2.6.0, PostgreSQL 16.2, Oracle client 21.14.0.0.0, ORACLE_HOME=/usr/lib/oracle/21/client64

The select SELECT t.request_id, CASE WHEN (t.geoloc.sdo_point.x IS NULL) THEN NULL ELSE t.geoloc END AS g FROM CITYLIGHT.REQUEST t;

works fine on Oracle dB.

Could you please help?

WIth best regards, Sergei

[mkgrgis]

@sggrigoriev , why do you think table FDW server option value and style is normal in your context?

[laurenz]

If you want to use an Oracle query, you must not specify the schema option. I thought that documentation was clear enough:

To define a foreign table based on an arbitrary Oracle query, set this option to the query enclosed in parentheses, e.g.

OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')

Do not set the schema option in this case.

[sggrigoriev]

If you want to use an Oracle query, you must not specify the schema option. I thought that documentation was >clear enough

You're absolutely right, and I'm not.

Thanks a lot! Now it works. Sorry for stupid question :-)

With best regards, Sergei

[laurenz]

No problem. You're probably not the only one who makes that mistake, and that issue might help others.