3 Replies Latest reply on Mar 2, 2017 9:54 AM by John Rodrigues

    Teiid error on Oracle Trunc function

    John Rodrigues Novice

      Teiid reported the following exception:

      Got exception [TEIID40095 TEIID31080 gosales_vdb3.Inventory_query_Time validation error: TEIID30068 The function 'TRUNC((TIME_DIMENSION.MONTH_KEY / 100), 0)' is an unknown form.  Check that the function name and number of arguments is correct.] while trying to deploy model to VDB [adfcorevdb]
      

       

      when it encountered the Oracle Trunc function in the following where clause:

      WHERE  ( time_dimension.month_key - Trunc(time_dimension.month_key / 100, 0) * 100 ) <> 0 
             AND ( time_dimension.month_key - Trunc(time_dimension.month_key / 100, 0) * 100 ) <> 0
      

       

      I was able to get past this error by adding pushdown functions for TRUNC in my subclass of org.teiid.translator.ExecutionFactory:

         @Override
         public void start() throws TranslatorException {
            addPushDownFunction(ORACLE_QUALIFIER, TRUNC, BIG_DECIMAL, BIG_DECIMAL, BIG_DECIMAL);
            addPushDownFunction(ORACLE_QUALIFIER, TRUNC, DATE, DATE, STRING);
            super.start();
         }
      

       

      My oracle data source model is setup as follows:

          <model name="GOSALES" type="PHYSICAL" visible="true">
              <property name="ADF data Source Model" value="true"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <property name="importer.includeSynonyms" value="true"/>
              <property name="importer.schemaPattern" value="GOSALES"/>
              <source name="GOSALES" translator-name="adfOracle" connection-jndi-name="java:/goSalesDataSource"/>
          </model>
      

       

      I wanted to know if it's possible to configure a Teiid model to push down all functions to Oracle by default, without having to explicitly specify them?