Repository navigation
Unable to parse ORACLE LISTAGG-Function in combination with OVER-Clause #1652
Description
Hi
The parser (version 4.5) reports
Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "BY" "BY"
at line 10, column 88.
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>
while parsing the following statement (reduced to the minimum with sample data included):
-- not parseable
WITH CTE_DUMMY_DATA(COL_TO_AGG, PART_COL) AS (
SELECT 'Foo', 1 FROM DUAL
UNION
SELECT 'Bar', 2 FROM DUAL
UNION
SELECT 'Baz', 1 FROM DUAL
)
SELECT
LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) OVER (PARTITION BY d.PART_COL) AS MY_LISTAGG
FROM cte_dummy_data d;This is fine ORACLE-Sql (see https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030) and returns something like
I was able to reduce the Problem to the OVER-Clause, as the following (semantically different) snippet is parseable:
-- parseable
WITH CTE_DUMMY_DATA(COL_TO_AGG, PART_COL) AS (
SELECT 'Foo', 1 FROM DUAL
UNION
SELECT 'Bar', 2 FROM DUAL
UNION
SELECT 'Baz', 1 FROM DUAL
)
SELECT
LISTAGG (d.COL_TO_AGG, ' / ') WITHIN GROUP (ORDER BY d.COL_TO_AGG) AS MY_LISTAGG
FROM cte_dummy_data d;Fix/Workaround would be appreciated.
Activity
wumpz commentedon Oct 28, 2022
within groupandoverare not supported at the same time. Why not use theorder byfor window functions? I am not quite sure if I understand the use ofwithin grouphere. This should work:andghe commentedon Oct 29, 2022
Hi @wumpz
Thank you for your reply. Unfortunately your suggestion is not feasible. According to Oracle's documentation (https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030) the WITHIN GROUP clause is not optional when using LISTAGG. The execution results in an ORA-02000 when the WITHING GROUP clause is omitted.
manticore-projects commentedon Oct 29, 2022
Greetings,
thank your for reporting.
I do use
LISTAGGon Oracle myself and thus will look into this issue soonest.wumpz commentedon Nov 2, 2022
@andghe unfortunately you are right: http://sqlfiddle.com/#!4/c6069c/4/0. Sorry I missed that.
feat: LISTAGG() with OVER() clause
Assorted fixes (#1666)