Repository navigation
Failed to parse LISTAGG + WITHIN GROUP + OVER from Oracle SQL query #1681
Description
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 ?
Activity
gabrielefantini commentedon Dec 5, 2022
I have the same issue.
manticore-projects commentedon Dec 5, 2022
Greetings, please use JSQLParser 4.6 Snapshot (from source) since this problem has been fixed only recently by PR #1666
Duplicate of #1652
gabrielefantini commentedon Dec 5, 2022
thanks a lot.
detienneZN commentedon Dec 8, 2022
Thanks for your answer,
Just one more question :