Oracle decode equivalent function to Postgres

So, I’m in migrating database from oracle to Postgres, and this is my error:

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

and here’s my code :

sqlQuery.append(" order by ");
            sqlQuery.append(" decode('pts.process_status', 'PTSPS_INPROGRESS', 1, 'PTSPS_SUBMITTED', 2, ");
            sqlQuery.append("            'PTSPS_DRAFT', 3, 'PTSPS_REJECTED', 4, 'PTSPS_APPROVED', 5) ");

do you have any clue?

From what I’ve read, some say to replace the decode function with COALESCE, but I don’t have any idea how the syntax is.

Answer

You could use a standard case expression:

order by case pts.process_status
    when 'PTSPS_INPROGRESS' then 1
    when 'PTSPS_SUBMITTED'  then 2
    when 'PTSPS_DRAFT'      then 3
    when 'PTSPS_REJECTED'   then 4
    when 'PTSPS_APPROVED'   then 5
end

Advertisement

Answer