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:

         public void start() throws TranslatorException {
            addPushDownFunction(ORACLE_QUALIFIER, TRUNC, DATE, DATE, STRING);


      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"/>


      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?