Postgres CASE statement not working in SQL Server

Question

I've written a query in postgres (on PGAdmin) and I'd like to put it in SSMS2012 for reporting purposes however it doesn't seem to allow the CASE statement, this is my query:

SELECT * 
FROM OPENQUERY (POSTGRESQL,
                'SELECT 
                     Table1 AS Table_1,
                     CASE Table2 
                        WHEN 75887 THEN ''1''
                        WHEN 75888 THEN ''2''
                        WHEN 75889 THEN ''3'' 
                        WHEN 75890 THEN ''4''
                        WHEN 75891 THEN ''5''
                        WHEN 75892 THEN ''6''
                     END AS Table_2,
                     DateTable1 AS DateTime
                 FROM SuperTable1 ')

And I'm getting the error:

OLE DB provider "MSDASQL" for linked server "POSTGRESQL" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].table_2" from OLE DB provider "MSDASQL" for linked server "POSTGRESQL".

I've noticed if I remove the whole case statement it has no issues retreiving the data in the columns in the SELECT statement and the whole thing (including the CASE statement) works fine in PGAdmin.

Perhaps you need to do CASE WHEN Table2 = 75887 THEN ...WHEN WHEN Table2 = ...? — jarlh
Is Table1 a field name? If not, you might have to quote it like ''Table1''. Also, I am not entirely sure if DateTable1 should be written as to_char(DateTable1, ''YYYY-MM-DD HH24:MI:SS'') - something to try — zedfoxus
table1 is the field name yes, unfortunately writing to_char(DateTable1, ''YYYY-MM-DD HH24:MI:SS'') didn't fix the issue, still getting the same error — Mike
Do you really have a column named Table2? - extremely strange naming conventions — a_horse_with_no_name

Juan Carlos Oropeza Juan Carlos Oropeza · Accepted Answer · 2015-10-19T13:18:13

Try simplify the query

Run 4 separated querys to determinate which one is giving you the problem. Also try using 1 instead of ''1''

SELECT Table1 AS Table_1

SELECT DateTable1 AS DateTime

SELECT 
    CASE Table2 
    WHEN 75887 THEN 1   
    END AS Table_2

SELECT 
    CASE Table2 
    WHEN 75887 THEN ''1''
    END AS Table_2

Postgres CASE statement not working in SQL Server

1 Answers