Failed to parse LISTAGG + WITHIN GROUP + OVER from Oracle SQL query

[detienneZN]

Hi,

We need to parse an Oracle SQL query (with LISTAGG + WITHIN GROUP + OVER syntax) :

When we trying to parse the following query ( from LISTAGG Oracle docs ) :

SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";

JSQLParser returns the followings exception :

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "BY" "BY"
at line 3, column 26.

Was expecting one of:

")"
","
"BINARY"
"BIT"
"CHAR"
"CHARACTER"
"DOUBLE"
"INTERVAL"
"JSON"
"SET"
"SIGNED"
"UNSIGNED"
"XML"
<DT_ZONE>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:33398)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:33231)
at net.sf.jsqlparser.parser.CCJSqlParser.Alias(CCJSqlParser.java:7148)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:6862)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:6887)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:6850)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:6176)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:6406)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:6054)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:6049)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:248)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:742)
at net.sf.jsqlparser.parser.CCJSqlParserUtil$2.call(CCJSqlParserUtil.java:317)
at net.sf.jsqlparser.parser.CCJSqlParserUtil$2.call(CCJSqlParserUtil.java:314)

Could you fix this type of query in JSQLParser ?

[gabrielefantini]

I have the same issue.

[manticore-projects]

Greetings, please use JSQLParser 4.6 Snapshot (from source) since this problem has been fixed only recently by PR #1666

Duplicate of #1652

[gabrielefantini]

thanks a lot.

[detienneZN]

Thanks for your answer,
Just one more question :

• What's the scheduled date for the 4.6 release delivery ?