function decode(text, unknown, integer, unknown, integer, unknown, integer, integer) does not exist

Euridice01 0
Viewed 16

I'm currently migrating some queries from oracle to postgres syntax. I noticed there is no equivalent to the oracle decode() function after seeing this error being returned:

Caused by: org.postgresql.util.PSQLException: ERROR: function decode(text, unknown, integer, unknown, integer, unknown, integer, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

but doing some digging, I read up that other suggestion was case equivalent.

oracle query:

"SELECT CODE " +
        "FROM SCHEMA.TABLE_NAME " +
        "WHERE TYPE='CURRENCIES' " +
        "ORDER BY decode(upper(code), 'EUR', 1, 'GBP', 2, 'USD', 3, 4)";

postgres query attempt:

"SELECT CODE " +
        "FROM SCHEMA.TABLE_NAME " +
        "WHERE TYPE='CURRENCIES' " +
        "ORDER BY case upper(code)" +
    "when 'EUR' then 1 " +
    "when 'GBP' then 2 " +
    "when 'USD' then 3" +
    "else 4";

I think I almost have it nailed done (please let me know if not) but I'm not sure if I can use the else 4 here? Is that right or needs adjusting here? What about the rest of the query that I've adjusted?

sql postgresql
1 Answers

The3 statement o

CASE WHEN ..THEN ...
WHEN .. THEN ...
END

So your code is missing an END at the end

"SELECT CODE " +
        "FROM SCHEMA.TABLE_NAME " +
        "WHERE TYPE='CURRENCIES' " +
        "ORDER BY case upper(code)" +
    "when 'EUR' then 1 " +
    "when 'GBP' then 2 " +
    "when 'USD' then 3" +
    "else 4 END";
nbk0
Related