I have a screen and a report showing what error codes it is getting based on the virtual number selected on this page. I want to paginate the records in the report but this code is not working. What is my mistake here, how can I fix it?
private static final String PAGINATION_SQL = " OFFSET ? ROW FETCH FIRST ? ROW ONLY";
private static final String[] ERROR_TYPE = new String[] {"BARRING", "UNKNOWN_NO", "NOTIYS", "SISERROR","REJECT","OTHER"};
private static final String ERROR_REPORT_SQL = "WITH ERROR_CODE_VR AS ({0}) SELECT * FROM ERROR_CODE_VR WHERE ERROR_CODE_VR.COUNT != 0 ORDER BY ERROR_CODE_VR.COMPANY_MSISDN DESC";
private static final String ERROR_REPORT_SQL_PART = "SELECT {0} as ERROR_DETAIL, {1} AS TARIFF, @REPORT_TABLE_NAME@.TARIH, @REPORT_TABLE_NAME@.COMPANY_MSISDN, SUM(@REPORT_TABLE_NAME@.{2}
public List<Report> getErrorCodeReport(ReportRequest request) {
try {
String innerSqlPart = "";
for (String errorStr: Arrays.asList(ERROR_TYPE)) {
innerSqlPart += MessageFormat.format(ERROR_REPORT_SQL_PART, '\''+errorStr + '\'', '\''+request.getTariff() + '\'', errorStr, "('"+request.getVirtualNumber()+"')");
if(!errorStr.equalsIgnoreCase(ERROR_TYPE[ERROR_TYPE.length-1])) {
innerSqlPart += " UNION ALL ";
}
}
String generatedSQL = generateUnionAllSql(innerSqlPart, request.getVirtualNumber());
String errorReportSql = MessageFormat.format(ERROR_REPORT_SQL, generatedSQL);
return this.JdbcTemplate.query(errorReportSql + PAGINATION_SQL, new Object[] { PAGE_RECORD_COUNT * request.getPageNumber(), PAGE_RECORD_COUNT}, new ErrorReportRowMapper());
} catch (Exception ex) {
log.error("getErrorCodeReport virtualNumber:{} {}",request.getVirtualNumber(), ex.getLocalizedMessage());
return new ArrayList<>();
}
}
