4 Replies Latest reply on Apr 4, 2019 5:07 PM by shawkins

    "cannot be pushed to source" error when calling source function, with parameters referred to table columns.

    cshong

      Teiid version: 12.0

       

      Source database: Microsoft SQL Server 2014

      Translator: sqlserver

       

      Lets assume, in Microsoft SQL server, I have a source function dbo.examplefunc(uniqueidentifier, date, bit). The data type of first parameter is uniqueidentifier (https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2014), the data type of second parameter is date, and the data type of third parameter is bit for boolean value. "dbo" is the schema name. The function return a data type, which the java equivalent is bigdecimal.

       

      Lets assume, in my VDB, I have the following DDL command:

       

      CREATE FOREIGN FUNCTION "dbo.examplefunc"(col1 string, col2 date, col3 boolean) returns bigdecimal;

       

      I tried to run the following queries:

       

      Query 1 (success without issue):

       

      SELECT MODEL1."dbo.examplefunc"('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', '2019-04-02', TRUE)

       

      Query 2 (Failed with error):

       

      SELECT MODEL1."dbo.examplefunc"(TAB1.STRINGCOL, TAB1.DATE, TRUE) FROM MODEL2.TABLE1 TAB1

       

       

      Query 1, which the function parameter was directly typed in by me and never refer to column of another table, run successfully without issue.

       

      In query 2, I passed values of columns of another table as function parameters, and I am sure that the table column data type match the function parameter data type. But, the query failed with error "cannot be pushed to source".

       

      Any solution?

       

      Note: The above model names, table names, column names, function names, and parameter names are not real names.