Group By and FORMATTIMESTAMP not translated
Hi all,
I am now trying to translate the below SQL to DB2 SQL. But seems the Group By clause and FORMATTIMESTAMP function was not translated successfully.
Could you please help to check and guide what I can do to in my DB2ExecutionFactory? Thanks in advance!
---------------------------------------------------------------------------------------------------------------
The original SQL:
SELECT PARSEDATE(CD.DATA_PROCESS_ADD_TIMESTAMP,'yyyy-MM-dd') AS PROCESS_DATE
,MAX(CD.DATA_PROCESS_START_TIMESTAMP) AS MAX_PROCESS_DATE
,SUM(CD.AMT_PLAN_PAID) AS PLAN_PAID
,SUM(CD.RX_COUNT) AS RXS
FROM BSC.DATA_DETAIL CD
WHERE CD.DATA_PROCESS_ADD_TIMESTAMP >= PARSETIMESTAMP('2012-01-01','yyyy-MM-dd')
AND CD.DATA_PROCESS_ADD_TIMESTAMP < PARSETIMESTAMP('2016-07-26','yyyy-MM-dd')
AND CD.CUSTOMER_NUMBER = 192
AND CD.DATA_STATUS_CODE IN (10,11)
AND CD.DATA_CICS_STATUS_CODE <>'HIS'
GROUP BY
PARSEDATE(CD.DATA_PROCESS_ADD_TIMESTAMP,'yyyy-MM-dd');
---------------------------------------------------------------------------------------------------------------
My tranlator file:
package com.mycompany.teiid.translator;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.teiid.language.Expression;
import org.teiid.language.Function;
import org.teiid.language.Literal;
import org.teiid.translator.SourceSystemFunctions;
import org.teiid.translator.Translator;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.jdbc.AliasModifier;
import org.teiid.translator.jdbc.FunctionModifier;
import org.teiid.translator.jdbc.db2.DB2ExecutionFactory;
@Translator(name = "mycompanydb2", description = "A translator for IBM DB2 Database customized for mycompany")
public class MycompanyDB2ExecutionFactory extends DB2ExecutionFactory {
@Override
public List<String> getSupportedFunctions() {
List<String> supportedFunctions = new ArrayList<String>();
supportedFunctions.addAll(super.getSupportedFunctions());
supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP);
return supportedFunctions;
}
@Override
public boolean supportsFunctionsInGroupBy() {
return true;
}
@Override
public void start() throws TranslatorException {
super.start();
registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new AliasModifier("VARCHAR_FORMAT"));
registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP,
new FunctionModifier() {
@Override
public List<?> translate(Function function) {
List<Expression> p = function.getParameters();
Expression var = p.get(0);
if (p.get(0) instanceof Function) {
Function f = (Function) p.get(0);
if ("convert".equalsIgnoreCase(f.getName())) {
var = f.getParameters().get(0);
}
}
String format = ((Literal) p.get(1)).getValue()
.toString();
// mm=>mi for db2
format = format.replaceAll("mm", "mi");
return Arrays
.asList("TO_CHAR(", var, ", '", format, "')"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
}
});
}
}
---------------------------------------------------------------------------------------------------------------
Error thrown when the translator runs:
Error: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 BSC: -551 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [{ts '2012-01-01 00:00:00.0'}, {ts '2016-07-26 00:00:00.0'}] SQL: SELECT g_0."DATA_PROCESS_ADD_TIMESTAMP", g_0.DATA_PROCESS_START_TIMESTAMP", g_0."AMT_PLAN_PAID", g_0."RX_COUNT" FROM "BSCA_SCHEMA"."DATA_DETAIL" AS g_0 WHERE g_0."DATA_PROCESS_ADD_TIMESTAMP" >= ? AND g_0."DATA_PROCESS_ADD_TIMESTAMP" < ? AND g_0."CUSTOMER_NUMBER" = 192 AND g_0."DATA_STATUS_CODE" IN (10, 11) AND g_0."DATA_CICS_STATUS_CODE" <> 'HIS']
SQLState: TEIID30504
ErrorCode: 0@
