PGSql没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换

Question

因此,im在将数据库从Oracle迁移到PGSql项目中,现在im修复了一些查询.我有一个这样的错误：

Caused by: org.postgresql.util.PSQLException: ERROR: function nvl(timestamp without time zone, timestamp with time zone) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

并且我在这个字符串生成器上发现了来自控制台的nvl错误

    public List<InsEmployeeInfoVo> load(int first, int pageSize,
            String sortField, SortOrder sortOrder, SearchFilter searchFilter) {
        SQLQuery query = null;

        try {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.append(" select ");
            sqlQuery.append("     pts.PERSON_TRAN_SING_ID, ");
            sqlQuery.append("     ext.INTERNALID PERSON_EXT_ID, ");
            sqlQuery.append("     people.internalid PERSON_ID, ");
            sqlQuery.append("     people.EMPLOYEE_NUMBER NIK, ");
            sqlQuery.append("     people.PER_INFORMATION1 PERSON_NAME, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_MEMBER_NO is not null ");
            sqlQuery.append("         then pts.INS_MEMBER_NO ");
            sqlQuery.append("         else ext.INS_MEMBER_NO ");
            sqlQuery.append("     end INS_MEMBER_NO, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_TOTAL_COVER is not null ");
            sqlQuery.append("         then pts.INS_TOTAL_COVER ");
            sqlQuery.append("         else ext.INS_TOTAL_COVER ");
            sqlQuery.append("     end INS_TOTAL_COVER, ");
            sqlQuery.append("     null INS_PLAN_NAME, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_BH_CARD_NO is not null ");
            sqlQuery.append("         then pts.INS_BH_CARD_NO ");
            sqlQuery.append("         else ext.INS_BH_CARD_NO ");
            sqlQuery.append("     end INS_BH_CARD_NO, ");
            sqlQuery.append("     case ");
            sqlQuery.append("         when pts.INS_BH_TOTAL_COVER is not null ");
            sqlQuery.append("         then pts.INS_BH_TOTAL_COVER ");
            sqlQuery.append("         else ext.INS_BH_TOTAL_COVER ");
            sqlQuery.append("     end INS_BH_TOTAL_COVER, ");
            sqlQuery.append("   null INS_BH_PLAN_NAME, ");
            sqlQuery.append("     pts.PROCESS_STATUS PROCESS_STATUS, ");
            sqlQuery.append("     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,1) PROCESS_STATUS_NAME, ");
            sqlQuery.append("     pts2.PERSON_TRAN_SING_ID PERSON_TRAN_SING_ID_2, ");
            sqlQuery.append("     pts2.PROCESS_STATUS PROCESS_STATUS_2, ");
            sqlQuery.append("     upl.PERSON_DATA_UPL_DTL_ID, ");
            sqlQuery.append("     upl.PROCESS_STATUS PROCESS_STATUS_UPLOAD, ");
            sqlQuery.append("     NVL(pts.UPDATE_ON, pts.CREATE_ON) INPUT_DATE, ");
            sqlQuery.append("     BTPN_INS_F_GET_CHECKER (people.internalid,:singSourceType,2) NOTE_APPROVE, pts.NOTE ");
            sqlQuery.append(" from WOT_PEOPLE people ");
            sqlQuery.append("     inner join WOT_ASSIGNMENT assign ");
            sqlQuery.append("         on assign.PERSON_ID = people.internalid ");
            sqlQuery.append("         and assign.PRIMARY_FLAG = 'Y' ");
            sqlQuery.append("         and (date_trunc('day', now()) between assign.EFFECTIVE_START_DATE and assign.EFFECTIVE_END_DATE) ");
            sqlQuery.append("     left join BTPN_INS_PEOPLE_EXT ext ");
            sqlQuery.append("         on ext.PERSON_ID = people.internalid ");
            sqlQuery.append("         and date_trunc('day', now()) between ext.EFFECTIVE_START_DATE and NVL(ext.EFFECTIVE_END_DATE, now()) ");
            

Answer 1

您可以尝试使用PostgreSQLCOALESCE函数（请参见9.17.2.COALESCE部分），而不是oracleNVL函数。