How to resolve SQL error when fetching data from a foreign data table in Postgres

Question

I have a foreign data table created with tds_fdw which maps a SQL View on an MSSQL server to a foreign table in Postgres. I can view and query the data in this table without any issue. But I'm getting this error when I try to query this table from a query which is joining the foreign table and several other tables.

Error is:

[2020-07-28 14:04:25] [HV00L] ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
[2020-07-28 14:04:25] [00000] DB-Library notice: Msg #: 156, Msg state: 1, Msg: Incorrect syntax near the keyword 'as'., Server: DB1DBSSVR1, Process: , Line: 1, Level: 15

This is how the SQL query looks like

SELECT COL1, COL2, COL3 FROM (
    SELECT a.COL1, b.COL2, c.COL3 FROM 
        PSQL_TABLE_1 a JOIN PSQL_TABLE_2 b ON a.ID = b.ID
        JOIN FOREIGN_TABLE f on b.ID = f.CODE WHERE a.COL5 = 'S'
    UNION ALL
    SELECT a.COL1, b.COL2, c.COL3 FROM 
        PSQL_TABLE_1 a JOIN PSQL_TABLE_3 b ON a.ID = b.ID
        JOIN FOREIGN_TABLE f on b.ID = f.CODE WHERE a.COL5 = 'S'
) 
DERIVED WHERE DERIVED.SEQ_NO = 'xxxx'

Note
Earlier this query used a local pg table which had the same name and structure as the foreign table and was working fine. This issue started after renaming it and creating the new foreign table with the same name and structure

Answer 1

Aah, now I see. Pretty sure it is this:

DERIVED WHERE DERIVED.SEQ_NO = 'xxxx'.

DERIVED is a keyword and there is an implied as before it. Change that alias to another name or quote it.

Answer 2

It seems that the issue was due to some data values in the fields I was using for the joins. The error went away once I trimmed the data. The new query looks something like,

SELECT COL1, COL2, COL3 FROM (
    SELECT a.COL1, b.COL2, c.COL3 FROM 
        PSQL_TABLE_1 a JOIN PSQL_TABLE_2 b ON a.ID = b.ID
        JOIN FOREIGN_TABLE f on btrim(b.ID) = f.CODE WHERE a.COL5 = 'S'
    UNION ALL
    SELECT a.COL1, b.COL2, c.COL3 FROM 
        PSQL_TABLE_1 a JOIN PSQL_TABLE_3 b ON a.ID = b.ID
        JOIN FOREIGN_TABLE f on btrim(b.ID) = f.CODE WHERE a.COL5 = 'S'
) 
DERIVED WHERE DERIVED.SEQ_NO = 'xxxx'

This query is returning values as expected. But I'm still wondering why did it give an error saying there is a syntax error near as.