Teiid execute immediate gives a parsing error when executing long queries

Question

I'm using virtual procedures to expose a REST API using teiid. In my virtual procedure i am using execute immediate to execute SQL queries which takes input parameters from the virtual procedure as filters to the where clause (dynamic where cluase). This works fine for small select queries but when the query length is above a particular length it gives an parsing error. Is there any solution for this problem? Is there any alternative way of implementing dynamic where clauses in my SQL query?

Lets assume that the fallowing query has around 4000 characters. this works fine.

    CREATE VIRTUAL PROCEDURE GetVals(IN filters string) RETURNS (json clob) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'get_vals')
    AS

    BEGIN execute immediate 
        'SELECT  JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(
            col1,
            col2,
            col3,
            col4,
            col5,
            col6,
            ....
    ....

            )) as "data" 
        ) as json FROM(
        SELECT SUM((CASE
                WHEN ((CASE
    .....
    ....
    .....   

                FROM ex_table AS ex
                  JOIN table1
                  ON ...
                 .....
              WHERE a=b AND ' ||  filters || '
          GROUP BY col)
        ) AB';            
    END

But as soon as I add more lines into above SQL query then it give an parsing error login an arbitrary line. There is nothing wrong with the syntax of my query. The only change I make is the length of the query adding more lines into it(eg. In my SELECT statement If I select onemore extra column this gives an parsing error)This happens only when I am using execute immediate to execute queries

Answer 1

What version or Teiid are you using? And what is your parsing exception?

If it is due to truncation, then you'll need to use a 9.1 or later release, which allows for longer evaluated sql strings - https://issues.jboss.org/browse/TEIID-4376