1 Reply Latest reply on Sep 9, 2016 10:35 AM by Steven Hawkins

    Group By and FORMATTIMESTAMP not translated

    Jun Cao Newbie

      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@