此函數相當于Oracle中的NVL且更方便,它完全可以取代SQL中的CASE.
用法:
COALESCE(A,B,C,D...);
說明:
若A為空;則取B,若B為空,則取C;...;依此類推.
用例:
sb.append(" select ");
sb.append(" COALESCE(t03.EMP_USER_NAME,'') as sellerName,");
sb.append(" COALESCE(t03.EMP_LOTUS_MAIL,'') as sellerNotesMail,");
sb.append(" COALESCE(t03.EMP_INTERNET_ID,'') as sellerIntranetId,");
sb.append(" COALESCE(t04.BUSINESS_UNIT_NAME,'') as busiNessUnit,");
sb.append(" COALESCE(t02.TOPT_JOB_ROLE_NAME,'') as jobRoleName,");
sb.append(" t01.TERRITORY_NAME as territoryName");
sb.append(" from");
sb.append(" topt.territory t01");
sb.append(" left join topt.job_role t02 on t01.ROLE_KEY=t02.ROLE_KEY ");
sb.append(" left join FRS.Resource t03 on t01.EMP_UID=t03.RESOURCE_EMPLOYEE_UID");
sb.append(" left join TOPT.BUSINESS_UNIT t04 on t01.BUSINESS_UNIT_KEY=t04.BUSINESS_UNIT_KEY ");
sb.append(" where ");
sb.append(" t01.TERRITORY_KEY=? ");
String sql = sb.toString();