search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
378 stars 101 forks source link

Incorrect syntax near the keyword 'as'. #167

Open eppesuig opened 6 years ago

eppesuig commented 6 years ago

Hello, on a postgresql 10, I created a view from a few foreign tables on sql server 2014 sp2. When I SELECT from that view without a WHERE condition, I get all records. When I SELECT using a restriction on a varchar field, I get this error message:

DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

Enableing trace on freetds, I found that SQL Server is complaing about this bad SQL query:

SELECT [t_bpid], [t_nama], [t_cadr] FROM [dbo].[ttccom100900] WHERE (([t_bpid] as text = 'CIA100478')) AND (([t_bpid] as text = 'CIA100478')))

From what I understood browsing tds_fdw source code, this might be a problem caused by the deparseRelabelType() that add an explicit cast using a non SQL Server syntax. Unfortunatly, I am not really sure about this, and I am not able to provide any patch.

Thank you, Giuseppe

SudoerWithAnOpinion commented 6 years ago

Using AS TYPE in the WHERE clause is, if I remember correctly, is unsupported. As you stated, this is the query being thrown to the server. Can you post your foreign table definition? If you are casting in your WHERE, HAVING, or JOIN you may need add explicit type casts.

SudoerWithAnOpinion commented 5 years ago

@GeoffMontee got another stale for you.