dremio

Dremio LISTAGG ORDER BY columns must be subset LISTAGG columns


I have a line of code in synapse SQL

SELECT REPLACE(STRING_AGG(COALESCE(REPLACE(DIMLIST.DISPLAYVALUE, '\', '\\'), ''), '@#@#@!?$')WITHIN GROUP(ORDER BY DHL.DIMENSIONHIERARCHY, DHL.LEVEL), '@#@#@!?$', DIMENSIONSEGMENTDELIMITER)

which returns data in format

---------2019----00000-2640
---210-10-2104-20-1005--2015-2016--210--0000
---930--------6120-410-00000-0000
-100--930-10-------6310-110-00000-3050
-------------00000-2102
---210-20-2104-20-2000--2020-2020--110--2640
---920-00-------6140-110--
---000------0000-----2102
---920--0000-10-----5120---
-208-00-----------00000-0000

If I run equivalent code

SELECT REPLACE(LISTAGG(COALESCE(REPLACE(DISPLAYVALUE, '\', '\\'), ''), '@#@#@!�$')WITHIN GROUP(ORDER BY DIMENSIONHIERARCHY, LEVEL), '@#@#@!�$', DIMENSIONSEGMENTDELIMITER)
--GROUP BY DIMENSIONSEGMENTDELIMITER

I first get error DIMENSIONSEGMENTDELIMITER is not being grouped, so if I add GROUP BY I then keep getting ORDER BY columns must be subset LISTAGG columns

What does this mean and how do I resolve it???

Expecting to return same synapse sql results.


Solution

  • Dremio LISTAGG is a more limited function than SQL STRING_AGG. I had to find a workaround.