Oracle Connector: LISTAGG function

[jrod2016]

Consider the following SQL directly against Oracle:

SELECT LISTAGG(last_name, '; ')
  WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
  MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

When running the same SQL, through the Teiid Oracle JDBC Connector, I get the following error:

 [Code: 0, SQL State: 50000]  Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered ", '; ') [*]WITHIN[*] GROUP (" at line 2, column 10.
Was expecting: <EOF> | "and" | "as" | "between" | "except" | "fetch" | "filter" | "from" | "in" | "intersect" ...

Seems the issue is with

Is there some alternate syntax/pushdown function declaration required to get this to work?

[shawkins]

LISTAGG is not directly supported.  The equivalent is to use

STRINGAGG(last_name, ';') ORDER BY hire_date, last_name

Pushdown support for STRINGAGG is currently limited.  If needed an

enhancement can be logged to be mapped to LISTAGG.

[jrod2016]

Logged a Jira ticket: [TEIID-4833] Oracle JDBC Connector: Support for LISTAGG - JBoss Issue Tracker

[shawkins]

Just to double check, the engine support would still be for stringagg and the JIRA would cover mapping that to LISTAGG for oracle pushdown correct?

[jrod2016]

The ideal scenario would be to support the Oracle LISTAGG syntax in the Teiid connector. However, if that's not possible at this time, we can go with pushdown of stringagg. This will require us to dynamically modify an oracle query to convert the listagg to stringagg before we deploy to Teiid.