Oracle转PostgreSQL：decode函数报错及COALESCE替换语法求助

嘿，这个问题我在Oracle转PostgreSQL的迁移项目里也碰到过！PostgreSQL确实没有Oracle专属的decode函数，不过咱们用标准SQL的写法就能完美替代，而且比COALESCE更适合这种多分支匹配的场景，我给你详细讲下：

首先得先指出你原SQL里的一个小错误：你把字段名pts.process_status加了单引号，这会让数据库把它当成一个字符串常量，而不是去读取这个字段的实际值，不管你的数据里是什么状态，永远匹配不上后面的条件，所以一定要去掉单引号！

方案一：用CASE WHEN替换（推荐） #

这是标准SQL语法，兼容性最好，逻辑也最直观，完全对应你原来decode的功能：

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

把它放到你的Java代码里就是这样：

sqlQuery.append(" order by ");
sqlQuery.append(" CASE pts.process_status ");
sqlQuery.append("     WHEN 'PTSPS_INPROGRESS' THEN 1 ");
sqlQuery.append("     WHEN 'PTSPS_SUBMITTED' THEN 2 ");
sqlQuery.append("     WHEN 'PTSPS_DRAFT' THEN 3 ");
sqlQuery.append("     WHEN 'PTSPS_REJECTED' THEN 4 ");
sqlQuery.append("     WHEN 'PTSPS_APPROVED' THEN 5 ");
sqlQuery.append(" END ");

如果没有匹配到任何状态，这个表达式会返回NULL，和你原来的decode行为完全一致。

方案二：用COALESCE实现（不推荐，仅作参考） #

如果你一定要用COALESCE，可以结合多个CASE表达式来模拟decode的逻辑，不过写法会繁琐很多：

COALESCE(
    CASE WHEN pts.process_status = 'PTSPS_INPROGRESS' THEN 1 END,
    CASE WHEN pts.process_status = 'PTSPS_SUBMITTED' THEN 2 END,
    CASE WHEN pts.process_status = 'PTSPS_DRAFT' THEN 3 END,
    CASE WHEN pts.process_status = 'PTSPS_REJECTED' THEN 4 END,
    CASE WHEN pts.process_status = 'PTSPS_APPROVED' THEN 5 END
)

COALESCE会返回第一个非空的结果，所以当某个条件匹配时，对应的CASE会返回数值，后面的就不会再执行了，效果和decode一样，但可读性远不如CASE WHEN。

内容的提问来源于stack exchange，提问作者WildanVEP